For many teams that use Power BI shared workspaces, having the ability to export the data in their reports and dashboards directly to Excel is crucial. This feature is important because it allows users to leverage the data visualization capabilities of Power BI while allowing them to slice and dice the same data using something perhaps more accessible to them. In this blog, I’ll show the different ways to export data from Power BI to Excel as a .xlsx or .csv file, including the third and newest method of doing so that was released in November of 2021.
What are the Methods of Exporting Data from Power BI to Excel?
Users export data directly from individual visualizations. Power BI has three formats that users can choose from to accomplish this task, and each has a different use case.
The first method is to export the data in a summarized format. This method maintains any filters or slicers applied to the visual and exports the summarized data used to create it. This means that any aggregations used by the visual will be maintained, for example, sums, averages, and medians.
The second method is to export the underlying data behind the visual. While this method also maintains all filters and slicers that are selected at the time of exporting, only the raw data from the Power BI dataset used to calculate what is shown on the visual will be exported. This will not export measures created in Power BI.
The third and newest format that data can be exported to is in the current layout of the visual. Note that this option is only available when using the table or matrix visual. This method not only maintains the current filter and slicer selections but also maintains the current layout of the table or matrix being exported.
How to Export Data from Power BI to Excel
Before attempting to export anything, we need to ensure that the functionality is turned on in Power BI Service. Do this by going into the settings of the Report in Power BI Service and choosing which methods of export your users should have the ability to use. This can also be done in the settings from Power BI Desktop.
Exporting the data from the selected visual follows the same initial steps for each of the methods. Click on the ellipses (…), select “Export data,” then the export method and file type. Reminder: the “Data with current layout” option is only available when using a table or matrix visual.
Let’s compare the files that were created from each of the three exports. Note that we are exporting from the matrix and that the Bikes category is collapsed in the visual; the screenshots below do not show the entire exported dataset.
In the summarized data export example below, we see that the summed totals for each row are maintained. Null values indicate that there is no data available for the row. Headers for each column have been reverted back to their original name that exists in the dataset.
Below is a screenshot from the export underlying data option. Clearly, this is the raw data that lives behind the visual and is used to calculate the aggregated view at the OrderDate level. Total Sales and Total Profits are measures that were created within Power BI, thus are not exported when using this method.
The final method is to export data from a table or matrix with its current layout, as shown in the screenshot below. This method maintains not only all of the slicers and filters that are applied but also all of the sorting and ordering, as well as collapsed or expanded columns on the visual. Notice in the screenshot that the Bikes column is collapsed, but its aggregations are still shown as well as each Product Name for the other Product categories.
Closing
Allowing your users to export data from Power BI to Excel is an important part of providing self-service tools to your organization. This feature can give people the freedom and ability to dive deeper into the data and get at the questions they are trying to answer.
