Power BI Troubleshooting: 30,000 Row Export Limit Workaround
Introduction
In Power BI, when creating any kind of reports, dashboards which will contain an X amount of rows of data in which your queries will be pulling, a lot of us will want to be able to deliver an exportable report in which we can share.
So once going to a specific visual or in most cases a table visual we click the … ellipsis button in which we are presented with the following options below;
https://lirp-cdn.multiscreensite.com/c1f89e50/dms3rep/multi/opt/Export-309x276-1920w.png
Figure 1.1 – Menu provided from … ellipsis button
If you have over 30,000+ rows of data in your visual you may see the following notification message below;
https://lirp-cdn.multiscreensite.com/c1f89e50/dms3rep/multi/opt/ExceedLimit-692x244-1920w.pngFigure 1.2 – Data Exceeds the limit message
Once clicking the OK button you will then be given the option to save the .CSV file to your desired location, and once opening you will see the export has only a maximum of 30,000 rows.
Why do I have this issue
The reason for this is because Power BI Desktop has an ultimate maximum of rows which can be pulled from an export which is 30,000 rows and this is because a .CSV file has this maximum within Power BI.
A .XLSX export on the other hand can have an export of 150,000 rows which is 5X better than the .CSV but the only issues is that you cannot by default do this within Power BI Desktop and also if you have data which exceeds 150,000 rows this can still cause issues.
Export Workaround
It’s quite a basic workaround but it does allow you to perform an export of every single row which you have.
On the left hand side you will see the pane which contains three types of views, the first of course being the main visuals you see, but the second option is the Data view which shows you a table of the entire data in which you have which is currently summarised within the Visual view.
https://lirp-cdn.multiscreensite.com/c1f89e50/dms3rep/multi/opt/Views-87x307-1920w.pngFigure 1.3 – Left hand pane displaying the view options
Here you can see the entire table data view of everything.
If you right click any column for example and select Copy Table, wait for a few seconds whilst the time loading icon takes its course.
https://lirp-cdn.multiscreensite.com/c1f89e50/dms3rep/multi/opt/DataTable-748x1265-1920w.PNGFigure 1.4 - Data view showing the Copy table function. Can see the statistics at the bottom showing 30,000+ rows
Open up a new excel spreadsheet and click paste.
Here you will now see all of your data and that’s including every row and every maximum row barrier exceeded.
https://lirp-cdn.multiscreensite.com/c1f89e50/dms3rep/multi/opt/Proff-1599x824-1920w.PNGFigure 1.5 - Paste information into spreadsheet with all rows included In the bottom right hand corner you can see the count is 47357 which is over the 30k rows maximum
Conclusion
Overall Power BI desktop is a powerful tool but can have its limits when it comes to exporting of large data from your data sets so this provides a simple workaround that can be used. Analyse in Excel is also another great option for this but as this requires a Power BI pro license, for those who are solely using Power BI Desktop as their source of creating custom reports this is a great workaround which can be used.