How to export Microsoft Power BI data to a Microsoft Excel file

For the Microsoft Power BI user, importing Microsoft Excel data is a common task. Once in Power BI, you analyze and report on that data. Occasionally, you might need to export a Power BI dataset to Excel. Perhaps a colleague wants to further analyze the data, or you want to analyze a bit more. The process of exporting to Excel can be easy or impossible depending on your status.

In this tutorial, I’ll show you several ways to export Microsoft Power BI datasets to the .xlsx Excel format. We’ll start with the easiest method and work through the more complex methods. The discussion will include instructions for the dataset’s owner and the end users because the export experience will be different.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft Power BI online and desktop with Microsoft 365 desktop on a Windows 10 64-bit system. Power BI doesn’t support the earlier .xls Excel format.

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 exporting the underlying dataset to administrators and report designers. Unless the owner of the dataset sets specific permissions for exporting, viewers — also known as consumers and end users — can’t export data via a dashboard or report.

Report designers, the owner of the dataset, control how end users can export data:

• End users can export summarized data.
• End users can export both summarized data and the underlying dataset.
• End users can’t export any data.

As an end user, if you can’t export the data, you can request the data by viewing contact information under the report title, as shown in Figure A.

Figure A

Click the Contact link to open a new message in your email client. Power BI will automatically insert a link to the report and the owner’s email. Fill in any additional information and send 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. Simply open the report in Power BI Desktop. To the left are three icons. Click Data, the one in the middle, to see the dataset.

To the right, as you can see in Figure B, you’ll see the Fields pane. Right-click the table and choose Copy Table. Doing so copies the dataset shown to the Clipboard. Open a blank Excel workbook and paste the dataset into a sheet. Send it off to your colleague.

Figure B

How to export data from a Power BI report

If you don’t have Power BI Desktop, you’ll have to work a bit harder and export from a report and what you get depends on the specific permissions set by the designer or Power BI’s default settings.

Figure C shows two visualizations based on a report named Sales by Month and Region report. That report, in turn, is based on a dataset — it’s the dataset you’re after.

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 might not return the full dataset. To export, do the following:

1. Hover your mouse over the tile or visualization in question.
2. You’ll see an ellipsis (…) in the upper-right corner. If you don’t, click above the visualization.
3. From the ellipsis dropdown, choose Export Data (Figure C).
4. In the resulting dialog, choose the .xlsx format from the File Format dropdown (Figure D). You can also choose to export a .csv file.
   1. Notice also that Power BI limits this particular export to summarized data. This means you can download the data seen in the visual, but not the complete dataset. If you want the complete dataset, you can stop here and contact the owner. You might also see a sensitivity warning. Your organization may have rules in place about how to handle sensitive data.
5. To export, click Export.
6. When prompted by your browser, click Open.

Figure C

Figure D

The open file will be an Excel .xlsx file. It’s unlikely that the summarized data will be what you’re after. 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 any filters.

If you’re lucky enough to grab all the data, you can use Excel to work with this data as you would any other data.

How to set export permissions in Power BI Desktop

As the designer, you might want to allow exports. If so, you’ll need Power BI Desktop to do so.

Click 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. To 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 entire dataset, not only the summarized version.

Figure E 

Once the permission is set to allow export of the dataset, the consumer will see the options in Figure F. Notice that this time, the Export Data options allow the consumer to download the underlying dataset shown in Figure G.

Figure F

Figure G

How to export from a Power BI dashboard

A Power BI dashboard is the final product that the designer wants end users to see. Exporting from a dashboard is similar to the above process for reports but unless permissions allow for exporting, you won’t get anything.

The ellipses dropdown will have more options and the list might limit you to exporting only as a csv file. If you’re able to download a .csv file, you can open this 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.

Exporting a Power BI dataset

Whether you can 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’re the owner, and you have Power BI Desktop, your export task is simple. If you are a consumer and the designer didn’t set export permissions for the dataset, you must contact the designer to get the dataset.