I’m going to show how to export data from visuals in Power BI Desktop that’s too big to be downloaded by the native functionality and therefore returns this error-message:
Check if you really need this
Although the method is fairly simple, there are simpler methods if you just need the raw data from your data model (and not the specific aggregations or measures that the visual contains):
- Use DAX Studio to download all tables from your data model at once
- Use DAX Studio to download specific tables from your data model (one by one)
- Or use R or Python to download specific tables if you’re comfortable with these languages. This method also allows scheduled refreshes in the service.
- Export via PowerShell is made very easy with Rui Romano’s PowerBIPS.Tool
Also, if your aim is to analyze that data in Excel and you have a Power BI Pro license or Premium, the “Analyze in Excel”-feature is probably a better option.
But if you’re still sure that you have to export data from visuals in Power BI Desktop, please read ahead.
Use DAX Studio to export the data to a txt file and let Power BI Desktop write the necessary query for you automatically. If you’re not familiar with DAX Studio, check out this great introductory blogpost.
- In Power BI Desktop, start recording in the Performance Analyzer
- Interact with your visual, so that a recording will be triggered.
- Open the recording details and copy the query. If the “Copy Query” is greyed out, close the file and re-open it. Then start recording and the interactions will trigger queries again.
- Open DAX Studio and connect to your PBI-file
- Paste the copied query into the query window
- Modify the TopN-figure: By default, only the top 501 rows will be retrieved (see line 8). Adjust that number to a higher value, so that all the rows of your specific query will be returned.
- Change the Output of the query to “File” and run the query
This will trigger a dialogue prompting for a path and filename to store.
- Wait until query has written the data to the file before trying to access its data (“Query Batch Completed”)
Enjoy and stay queryious 😉