Have you ever wanted to export a table from Power BI Desktop into Excel just to make sure the DAX you’ve written is performing as expected but ran into this error message: “Data exceeds the limit – Your data is too large. Some data sampling may occur”?
Most probably this has occurred because you’ve got more than 30,000 rows of data in your table that you’re trying to export. In the example shown below I’ve actually got 30,001 rows of data – a row containing column headers plus 30,000 rows of actual data.
If I now change the row count in my “Table To Export” table to 29,999 rows of actual data using a simple filter I have no issues exporting the data. This brings the grand total to 30,000 rows (29,999 rows of actual data plus a row containing columns headers).
But what if you really need to export more than 30,000 rows of data? Well there is a way…
Firstly make sure you’ve got DAX Studio installed on your machine.
Now within Power BI Desktop click “View” and then “Performance analyzer”.
This will open a new window pane called “Performance analyzer”.
Now click “Start Recording” and then click “Refresh Visuals”. Next within the Performance analyzer window locate the name of the visualisation which in my case is “Table To Export” and click the small “+” symbol next to it.
Next go ahead and grab the DAX query which has been used to construct the table. Click on the link “Copy query” which will copy the DAX query to your clipboard.
Next you’ll need to launch DAX Studio from within Power BI Desktop.
To do this on the top ribbon in Power BI Desktop click “External Tools” and then click “DAX Studio”.
Now within DAX Studio paste the copied query from your clipboard into the query window.
Next remove the variable which limits the number of rows which will be returned (TOPN) and replace the “EVALUATE” statement with “_DS0Core” as per the snip below.
Next change the output of the query to be executed from “Grid” to “File”.
Finally click the “Run” button and let DAX Studio Export your data containing at least 30,000 rows to a CSV file. Simple!
I hope this helps with any debugging you might need to perform in Power BI Desktop!
