Many Power BI users ask often about how to export the data of a visual especially when it exceeds the limit.
As you know, Power BI Desktop let you export the data of a visual by clicking in the “…” then “Export data” to a .CSV file. Unfortunately and according to Microsoft documentation, there is a limit of 30 000 rows that can be exported.
One of the solution is to create a calculated table by using DAX functions like ADDCOLUMNS or GROUPBY in order to summary the same columns of the visual that we want to get data then copy-paste it in a spreadsheet. However, using this method has two inconvenient:
- If there are many columns, writing the DAX statements can take a lot of time
- In many cases, the calculated table won’t be used in the data model (if we create it only for exporting data)
The other solution is to use the Performance analyzer pane. This feature was added in the May version of Power BI desktop, in order to help analyst to improve report if it is running slowly. One of the option of this pane, it generate the query executed for any visual in the report and export it as a text. This query can be used to create a calculated table. Let see how it works by using the example bellow.
In this example, we have a model with 2 tables: TabBill which list the amount of 500 000 bills and TabCustomer which list the customer of each bill.
In the report, we have a matrix visual which list all bills with an amount less or equal to 570 of all customers. The number of rows is 222 526.
If I want to export data from this visual, I get the following warning message “Data exceeds the limit: You data is too large. Some data sampling may occur.”.
To get the data of the visual, we follow these steps:
- Activate the Performance Analyzer pane
- Click on “Start recording” then “Refresh visuals”
- Copy the query of the matrix
4. Click in the data pane on “New Table” then paste the copied query
5. After pressing Enter, we will get an error message
6. In the query, we must delete the word “DEFINE”, change the word “EVALUATE” to “Return”, delete the function TOPN and its arguments and delete the function ORDERBY and its arguments. Don’t make attention to the red underlines.
And here is the final result:
In this example, I used a matrix visual. If instead of a matrix we use a table, we don’t delete all the TOPN statements, but have to let the SUMMARIZECOLUMNS part, which correspond to the second argument of the function TOPN
As we can see in the previous picture, we have more than 222K rows and there are 3 extra-columns that are used by the query to distinct between the subtotals and totals rows from the data rows.
7. Filter the empty cells
8. Copy the table and past it in a spreadsheet
And that is it! We have exported our 222K.
I hope that you will appreciate this solution. If you have any question, just comment below.
Keep learning and see you next time 😄