There are several ways to export data from Power BI to Excel. The method you choose will depend on how much data you need to export, as the system allows you to export anything from the data behind a single visual to exporting the data behind the entire reporting dashboard.
Power BI can export data to Excel from Power BI Desktop by clicking the … button at the top right of each visual. Exported data is unformatted and limited to the data required to present that visual or by Row Level Security. Full datasets can be exported to Excel from Power BI by creating a Matrix or Table Visual.
In addition to ability of exporting data to Excel, users can create live links to Power BI data using Excel Pivot tables, or embed data into PowerPoint presentations.
Microsoft makes it very easy for users to prep and blend data once in Power BI and present it in many different formats.
Let’s take a look!
Table of Contents
Determining How Much Data to Export from Power BI to Excel
Because Power BI provides users with so many different ways to export data the first thing that people need to do is to determine how much data they need to bring into Microsoft Excel.
The first limitation to be aware of is that Excel has a limit of 1,048,576 rows by 16,384 columns per worksheet.
Meaning that if you have over 1 million rows of data in your Power BI dataset, you will not be able to export the full amount of data into an Excel file. You may have to split the data into multiple sheets, or connect Microsoft Excel directly to a Power BI dataset. If you enable a live connection, you can interact with Power BI data through the use of an Excel pivot table.
Exporting Power BI Data to Excel from a Single Visual
All visuals in Power BI allow you to click the … button in the top right corner of the visual to Export data.
To view the … button users must move their mouse over the visual.
If you do not see the … button in the top right a visual make sure that the corner of the visual isn’t behind another visual. Try to move the visual or reduce the size of it to ensure that it is not behind another chart or graph.
Using this method has some limitations.
- You can only export to a .csv with this method. No other file formats are supported.
- Data is not exported with formatting.
- The amount of data is limited to the amount of data the visual is presenting.
The benefit of this method is that you can limit the total amount of exported data when working with data sets that exceed the limitations of Excel.
You can add filters and slicers to give users the ability to export the underlying data.
In addition, security that is established using Row Level Security or RLS in Power BI passes through to the exported data. This ensures that users can only export and work with data that they have access privileges to see.
Export All Data from Power BI to Excel
Because Power BI reports are built from data models, a collection of different tables or data sources it does not provide an option to Export a full dataset. As a workaround, create a table visual that contains all of the columns of data that you want to export.
The table visual does not give developers many formatting options, but it will show all rows of data that are within a dataset. You will need to manually select each of the columns to add.
You could also use a Matrix visual for a similar effect.
Disable subtotals and grand totals for an even more tabular export option.
Note: You can add this visual on a separate tab of the Power BI dashboard and either hide it from users or add slicers to let people filter it and then export the data set that is most important to them.
Limiting the Amount of Data Exported from Power BI to Excel
If you are using a very large dataset, you can first filter the data in Power BI by using a slicer or set of slicers or create a summarized visual that will allow you to export the data that you need after the calculations have already been performed.
There are two different filtering methods available.
Users can click on a visual to select it, and then apply Filters from the Filters pane of Power BI.
For a more curated approach, Power BI developers can add slicers directly into the dashboard. The example below adds a slicer visual with the country field added to it. The effect is the same as using a filter, but is much more visually appealing.
Slicers are our recommended approach for adding filters to Power BI. Many users are unaware of the Filter pane if they are non-technical or new to the platform.
Slicers can be adjusted for a number of different styles, such as multi-select lists, drop downs, date range pickers etc.
Copy and Paste Data from Power BI to Excel
Power BI allows you to copy data from a number of visuals and paste the results into Excel. Not all visuals support it, and some only partially support it. The Matrix visual is the most flexible.
Select a column of a Matrix visual by clicking on the Total row at the bottom. This will select the entire column.
You can also click on the left most row to select the full row.
Hold Down Ctrl and click to select multiple rows or columns in a Power BI Matrix Visual.
Even though the left most column is not selected in the previous example it is pasted along with the other column headers.
This is the fastest way to export data from Power BI to Excel without having to re-create a pivot table with columns of data in a .csv file.
Power BI Analyze in Excel and Export to PowerPoint or PDF
The Power BI Service provides users with 3 ways to Export data.
- Analyze in Excel – PowerBI will automatically generate a pivot table connected to the Power BI dataset using Excel Online.
- PowerPoint – Users can either export a static image of a report to embed into PowerBI or embed the entire interactive dashboard into a PowerPoint file with a live connection.
- PDF – Exports an individual report dashboard page, or all dashboard pages to a PDF File. This looks like each tab of the Power BI report was printed to a page of the PDF.
Exporting from Power BI to PowerPoint can be an exceptionally powerful way to sync data for management reporting, ensuring that data will match between a reporting dashboard and elements that are presented in PowerPoint slides.
Here’s a great video to learn even more about using PowerPoint and Power BI together.
How to Enable Export to Excel in the Power BI Service
To enable Export to Excel from the Power BI Service, click the Settings Cog from PowerBI.com and navigate to Admin Portal. Click on Tenant Settings and choose to enable Export to .xlsx . There are additional options to enable or disable exporting to other file formats.
This brings up the Tenant Settings
Power BI Tenant Settings are the policies and preferences applied to the entire organization. It is a level above individual workspaces which are collections of reports and dashboards that are typically created for individual departments.
From Tenant Settings, a Power BI admin can enable or disable the ability to Export to .csv, Export to .xlsx, along with a number of other formats.
Disabling these settings will turn off the ability to export for the entire organization.
How to Disable Export to Excel on a Published Power BI Report
After a report has been published, you can navigate to the report on the Power BI Service, open it and change settings that allow you to export the underlying data.
Developers have the option to only allow exporting summarized data or they can completely disable the ability to export underlying data.
Conclusion
To export data from Power BI to Excel, users can click the … symbol at the top right of a visual.
While there is no single button to export all of the data behind a Power BI data model, users can create a Matrix or Table visual that includes all of the columns that a user may be interested in exporting. This can be placed on an export data tab of the dashboard along with slicers to let people export only the data that they need.
Power BI will maintain Row Level Security or RLS when determining which data to allow users to export.
As an alternative to exporting data, Excel allows users to analyze in Excel and create a live connection between a an Excel pivot table and a Power BI dataset.