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:
Export data from visuals
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.
Solution
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.
Instructions
-
- In Power BI Desktop, start recording in the Performance Analyzer
Enable tracing in Performance Analyzer
Start Recordingin 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.
Copy Query in Performance Analyzer
- Open DAX Studio and connect to your PBI-file
Connect DAX Studio to PBI file
- Paste the copied query into the query window
Paste Query
- 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.
Adjust TopN
- Change the Output of the query to “File” and run the query
Output to file and run
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”)
Query is completed
- In Power BI Desktop, start recording in the Performance Analyzer
Enjoy and stay queryious 😉
Hi Imke,
Thank for sharing this idea.
You can do the same process directly into Power BI without using DAX Studio :
https://numidiabi.wordpress.com/2019/06/16/power-bi-performance-analyzer-pane-to-export-all-data-of-a-visual/
Very nice, thanks for the link!
/Imke
You posted this one because of the Facebook group discussion? I was impressed to see you on there!!!
Pingback: Exporting Large Data Sets from Power BI – Curated SQL
Hi Imke, another alternative is using PowerBIPS.Tools and the cmdlet “Export-PBIDesktopToCSV -daxQuery “DAX””
https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS.Tools/PowerBIPS.Tools.md#sample-script—export-pbi-desktop-to-sql-server-or-csv
Thanks Rui, included it in the article as well 😉
Hi Imke,
Another alternative is creating a link to a paginated report.
You can generate the url in DAX and pass parameters and render format into the URL.
I tested it on Power BI Report Server and it works well.
I think it should work on Power BI Service too.
Thanks for the tip/
/Imke
Is it possible to export data from visuals using python ?
Never tried it, but don’t know why it should not be possible.
Just integrate an export command into the code for the visual.
/Imke
Incredible find. I was finally able to download all the data from PowerBI using daxstudio. Never really looked into how powerful this Performance Analyzer is. Thanks
how big was the data ? near to 5lakhs records?