CODEWITHSUNDEEP
powerbipowerquerym
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

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

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

Limit docs:
https://learn.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

Upvotes: 2

Views: 25941

Answers (3)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

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

enter image description here

Change the output to file:

enter image description here

And run the following code:

EVALUATE
'MyTable'

enter image description here

And save the file in the desired output.

enter image description here

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

Upvotes: 1

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

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)

enter image description here

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

Upvotes: 3

Naresh Bhansali
Naresh Bhansali

Reputation: 11

Please find below solution to export Million of records from Power BI Visuals.

  1. Go to Performance analyser in Power BI Desktop and click on particular table/Slicer and then copy Query.
  2. We copy the query from Performance analyser for our required Table/Dax.
  3. We will open power Pivot in excel (Its an Add on in excel , available on Microsoft side )
  4. Connect to Analysis services ( Go to From Databases  From Analysis Services or Power Pivot )
  5. Enter the Server name
  6. Choose Database.
  7. Now it will ask you to put MDX Query.
  8. Paste the query which is copied in step 2.
  9. Change the top limit (by default it is 501  change is to as per requirement  I have tested for 3.2 M rows)
  10. Click on Finish.
  11. Power Pivot will show you all data (In Millions)

For Add on Power Pivot : Download from
https://www.microsoft.com/en-in/download/confirmation.aspx?id=43348

From Excel : https://blog.crossjoin.co.uk/2018/02/05/creating-excel-data-dump-reports-from-power-bi/

Upvotes: 0

Related Questions