Export large amount of data from Power BI desktop visuals

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):

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

    1. In Power BI Desktop, start recording in the Performance Analyzer 

      Enable tracing in Performance Analyzer

      Start Recordingin Performance Analyzer

    2. Interact with your visual, so that a recording will be triggered.
    3. 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

    4. Open DAX Studio and connect to your PBI-file

      Connect DAX Studio to PBI file

    5. Paste the copied query into the query window

      Paste Query

    6. 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

    7. 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.

    8. Wait until query has written the data to the file before trying to access its data (“Query Batch Completed”)

      Query is completed

Enjoy and stay queryious 😉

Comments (6) Write a comment

  1. You posted this one because of the Facebook group discussion? I was impressed to see you on there!!!

    Reply

  2. Pingback: Exporting Large Data Sets from Power BI – Curated SQL

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz