How to export Microsoft Power BI data to a Microsoft Excel file
For Microsoft Power BI user, importing Microsoft Excel data is a common task. Once in Power BI, you analyze and report on this data. Sometimes you may need to export a Power BI dataset to Excel. Maybe a colleague wants to take the data analysis deeper or you want to analyze a bit more. The process of exporting to Excel may or may not be easy depending on your status.
In this tutorial, I’ll show you several ways to export Microsoft Power BI datasets to Excel .xlsx format. We will start with the simplest method and go through the more complex methods. The discussion will include instructions for the dataset owner and end users, as the export experience will be different.
SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I’m using Microsoft Power BI online and desktop with Microsoft 365 desktop on a 64-bit Windows 10 system; Power BI does not support the earlier Excel .xls format. For your convenience, you can follow along by downloading the .xlsx Excel demo.
Who can export Power BI datasets?
Power BI is an organizational tool, so you might think that anyone with a link to a dashboard can export the underlying dataset, but that won’t always be the case. By default, Power BI restricts the export of the underlying dataset to administrators and report designers. Unless the dataset owner sets specific permissions for exporting, viewers, also known as consumers and end users, cannot export data through a dashboard or report.
Report designers, owners of the dataset, control how end users can export data:
- End users can export summary data.
- End users can export both summary data and the underlying dataset.
- End users cannot export any data.
As an end user, if you cannot export the data, you can request the data by displaying the contact information below the report title as shown in Figure A.
Click the Contact link to open a new message in your email client. Power BI automatically inserts a link to the report and the owner’s email. Fill in any additional information and submit as you normally would. If you’re lucky, the owner has the data in an excel .xlsx or .cvs file and can email or share it quickly.
How to copy a table in Power BI desktop
If you have Power BI Desktop, you can make a copy of a report’s dataset. Just open the report in Power BI Desktop. To the left are three icons. Click Data, the middle one, to see the dataset.
Right, as you can see in Figure B, you will see the Fields pane. Right-click the table and choose Copy Table. This copies the displayed dataset to the clipboard. Open a blank Excel workbook and paste the dataset into a sheet. Send it to your colleague.
How to export data from a Power BI report
If you don’t have Power BI Desktop, you’ll have to work a little harder and export from a report and what you get depends on the specific permissions set by the designer or Power BI defaults.
Figure C displays two visualizations based on a report named Sales Report by Month and Region. This report, in turn, is based on a dataset – that’s the dataset you’re looking for.
If the owner set export permissions in Power BI desktop when creating the report, you should be able to export the dataset. The process is simple, but it may not return the full data set. To export, follow these steps:
- Hover your mouse over the thumbnail or visualization in question.
- You will see an ellipsis (…) in the upper right corner. If not, click above the visualization.
- From the ellipsis drop-down list, choose Export Data (Figure C).
- In the resulting dialog, choose the .xlsx format from the File Format drop-down list (Figure D). You can also choose to export a .csv file.
- Also note that Power BI limits this particular export to summary data. This means you can download the data shown in the visual, but not the full dataset. If you want the full data set, you can stop here and contact the owner. You may also see a sensitivity warning. Your organization may have rules in place on how to handle sensitive data.
- To export, click Export.
- When prompted by your browser, click Open.
The opened file will be an Excel .xlsx file. Summary data is unlikely to be what you are looking for. Check step 4 above before giving up. If you filtered the visualization, Power BI will export the results of that filter. Return to the report and remove all filters.
If you are lucky enough to enter all the data, you can use Excel to work with that data as you would with any other data.
How to set export permissions in Power BI Desktop
As a designer, you might want to allow exports. If so, you’ll need Power BI Desktop to do this.
Click on the File menu and choose Options and Settings. In the resulting window, select Options. In the left pane, in the Current File section, select Report Settings. On the right, select the appropriate export settings as shown in Figure E and click OK.
As you can see, this report allows end users to download the full dataset, not just the summary version.
Once the permission is set to allow the dataset to be exported, the consumer will see the options in Figure F. Note that this time the data export options allow the consumer to download the underlying dataset presented in G-figure.
How to export from a Power BI dashboard
A Power BI dashboard is the end product that the designer wants end users to see. Exporting from a dashboard is similar to the process above for reports, but unless permissions allow exporting, you won’t get anything.
The ellipsis dropdown will have more options and the list may limit you to exporting only as a csv file. If you can download a .csv file, you can open that file in Excel by clicking the Data tab. In the Get & Transform Data group, click Get Data. Then choose From File and finally From Text/CSV.
Export a Power BI dataset
The ability to download the entire dataset from a report or dashboard depends on your relationship to the data and the export permissions set by the owner. If you are the owner and have Power BI Desktop, your export task is simple. If you are a consumer and the designer has not set export permissions for the dataset, you must contact the designer to obtain the dataset.