When a client wants the underlying data from a PowerBI live connection exported into Excel—with filters applied—what do you do? “Extract Underlying Data” in the PowerBI Service doesn’t filter all the data. That’s because there’s a limit to exporting, especially on the live connection, and the data doesn’t have any unique key in the transaction tables.
Here’s what Microsoft has to say about the limitations and considerations for Power BI Desktop and the Power BI service, including Power BI Pro and Premium:
- To export the data from a visual, you need to have Build permission for the underlying dataset.
- The maximum number of rows that Power BI Desktop and Power BI service can export from an import mode report to a .csv file is 30,000.
- The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.
Export using underlying data won’t work if:
- The data source is an Analysis Services live connection.
- The version is older than 2016.
- The tables in the model don’t have a unique key.
- If an administrator or report designer has disabled this feature.
- If you enable the Show items with no data option for the visualization Power BI is exporting.
When using DirectQuery, the maximum amount of data that Power BI can export is 16 MB uncompressed. An unintended result may be that you export less than the maximum number of rows. This is likely if there are a lot of columns or the data is difficult to compress.
Other factors increase file size and decrease the number of rows Power BI can export.
Now that we know the limitations, let’s look at some scenarios in which we’ve encountered it.
Power BI Export to Excel Not Working: Four Scenarios and Solutions
Every solution depends on multiple factors, such as visuals, columns, and logic used. That said, here are four scenarios with solutions to the problem of Power BI export to Excel not working.
Problem: Extracted data not filtered as in the report.
To combine multiple tables for different logic, it has been decided to create a master table and relate it to the transaction tables. The calculation logic is maintained in transaction table. The master table columns are used in the visuals and filters. The values and logic are given in the transaction tables.
When the filter is applied, it automatically filters the transaction table, since they’re related. However, while extracting the data to Excel, the data is not being filtered like its related column in the master table.
Solution: Replace the column from the master table in the visual with the related column from the transaction table.
Problem: Values from the extracted Excel don’t match data values from the report.
The extract will provide only the unique values from the data sets. So all the rows on which the aggregation logic is calculated are not being extracted to Excel.
Solution: Created an auto-incremented unique identification ID for all the transaction tables to extract all the rows available in the table.
Problem: Client does not want to see all internal calculations or references.
Solution: All unwanted columns from each table individually ‘hidden from client view’ so that the extract does not fetch those columns.
Problem: Logic calculated on data, when extracted to Excel, is applicable for each line. So some logic, such as percentage calculation wasn’t displaying correct values in the export.
Solution: All the columns needed in the extract are included in to the logic and all the other columns are hidden. As the logic now includes all the columns in the extract, the percentage displayed in the extract is the same as the value from the report.
The Best Power BI and Visualization Solutions
If you’re having problems getting the right view of your data, or your visualization dashboard needs an update, consider using a third-party specialist. They can often be less costly than creating your own systems in-house.
If you have additional questions about how to handle data extraction, filtering, and/or visualization, contact Tek Leaders. Our team of visualization experts can also create custom dashboards that enable you to generate robust visuals and make stronger evidence-based decisions. You can reach us by email directly.
Shashank Reddy Tummala.