Export million rows table from Power BI overcome the limit of 150k rows

Question

Is there a way to overcome the Power BI export max limit of 150k rows?

Limit docs:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data#limitations-and-considerations

Voting for PBI improvement:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18432742-increase-export-data-limits

Solution 1

It is possible to export a table of millions of rows from Power BI Desktop to a local disk with the help of Daxstudio.

Change the output to file:

And run the following code:

EVALUATE
'MyTable'

And save the file in the desired output.

You will see the progress as the table is being dumped to disk.

Solution 2

It is possible to export data from Power BI to SQL Server without any limit. You should be familiar with R and SQL Server to do that. The example below exports 201k rows directly from PBI to SQL Server. Install RODBC package in R. For those who want to do that from scratch, please check the reference links.

Here is an example. Generate a test table in Power BI with 201k rows:

let
    Source = List.Generate(()=>1, each _ < 201001, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

The table is one Column1 with values starting from 1 to 201001. So beyond the PBI limit.

Out with that through R. Menu Transform / Run R Script. Paste the code:

library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server;server=.\\SQLEXPRESS;Database=MyDataBase")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="MyR_table",rownames=FALSE, safer=FALSE, append=FALSE)
close(conn)

It will export the entire M table to SQLEXPRESS (or any SQL Server that you provide) to database MyDataBase to table MyR_table (the table is created dynamically, does not have to be created first on SQL Server). In my case, it dumped the whole test table of 201k rows in 8 and a half minutes.

Links for further reference:
http://biinsight.com/exporting-power-bi-data-to-sql-server/
https://www.youtube.com/watch?v=ANIZkTZO3eU

Comments

[Foxan Ng]

Someone has already shared a workaround if you scrolled through the comments.

[dim_user]

Do you happen to have the uodated link for the limits?