For Microsoft Power BI users, enter Microsoft Excel data is a common task. Once in Power BI, you analyze and report on that data. Sometimes you may need to export a Power BI dataset to Excel. Perhaps a colleague wants to analyze the data more deeply, or you want to do a little more analysis. Exporting to Excel may or may not be easy depending on your state.
In this tutorial, I will show you several ways to export a Microsoft Power BI dataset to the .xlsx Excel format. We will start with the easiest method and work with more complex methods. The discussion will include guidance for dataset owners and end users as the export experience will be different.
UNDERSTAND: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)
I am using Microsoft Power BI online and desktop with Microsoft 365 desktop on Windows 10 64-bit systems; Power BI does not support the earlier Excel .xls format. For convenience, you can follow at download demo .xlsx Excel.
Who can export Power BI datasets?
Power BI is an organizational tool, so you might think that anyone with a link to the dashboard can export the underlying dataset, but that’s not always the case. By default, Power BI restricts basic data set exports to administrators and report designers. Unless the owner of the dataset sets specific permissions to export, viewers – also known as consumers and end users – cannot export data via dashboards or reports.
The report designer, the owner of the data set, controls how the end user can export the data:
- End users can export summary data.
- End users can export both summary data and basic datasets.
- The end user cannot export any data.
As an end user, if you are unable to export the data, you can request it by viewing the contact information under the report header, as shown in Picture A.
Picture A
Click the Contact link to open a new message in your email client. Power BI will automatically insert a link to the owner’s email and report. 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 calculator
If you have Power BI Desktop, you can create a copy of the report’s dataset. Just open the report in Power BI Desktop. On the left are three icons. Click Data, the one in the middle, to see the dataset.
On the right, as you can see in Figure BUT, you’ll see the Fields pane. Right-click the table and select Copy Table. Doing so copies the displayed dataset to the Clipboard. Open a blank Excel workbook and paste the data set into a worksheet. Send it to your colleagues.
Figure BUT
How to export data from Power BI reports
If you don’t have Power BI Desktop you will have to work a little harder and export from reports and what you get depends on specific permissions set by the designer or default Power settings BI.
SIZE shows two visualizations based on a report named Monthly Sales Report and Area report. That report, in turn, is based on a dataset – that’s the dataset you’re tracking.
If the owner sets export permissions in the Power BI desktop when creating the report, you’ll be able to export the dataset. The process is simple, but it may not return the full data set. To export, do the following:
- Hover over the cell or image in question.
- You should see an ellipsis (…) in the upper right corner. If you don’t, click above the image.
- From the ellipsis drop-down menu, select Export data (SIZE).
- In the resulting dialog box, select the .xlsx format from the File Format drop-down menu (Visualization). 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 visual data, but not the complete data set. If you want the complete dataset, you can stop here and contact the owner. You may also see a sensitivity warning. Your organization may have rules about how sensitive data is handled.
- To export, click Export.
- When prompted by your browser, click Open.
SIZE
Visualization
The open file will be an Excel .xlsx file. It’s unlikely that summary data will be what you’re after. Check out Step 4 above before giving up. If you have filtered a visual image, Power BI will export the results of that filter. Go back to the report and remove any filters.
If you’re lucky enough to get all the data, you can use Excel to work with it like you would with any other data.
How to set export permissions in Power BI Desktop
As a designer, you may want to enable export. If so, you will need Power BI Desktop to do so.
Click the File menu and select Options and Settings. In the window that appears, select Options. In the left pane, under Current Files, select Report Settings. On the right, select the appropriate export setting, as shown in Figure E and click OK.
As you can see, this report allows the end user to download the entire dataset, not just the summary version.
Figure E
Once permissions are set to allow data set export, consumers will see options under Figure F. Note that this time, the Export Data options that allow consumers to download the underlying dataset shown in WOOD Figure.
Figure F
WOOD Figure
How to export from the Power BI console
The Power BI dashboard is the final product the designer wants the end user to see. Exporting from dashboard is similar to the above process for reports but unless permission allows export you will get nothing.
The drop-down ellipse will have more options, and the list may limit you to only exporting as a csv file. If you can download the .csv file, you can open it in Excel by clicking the Data tab. In the Get & Transform Data group, click Get Data. Then select From File and finally From Text/CSV.
Exporting a Power BI data set
Whether you can download an 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 you have Power BI Desktop, your export task is very simple. If you are a consumer and the designer did not set export permissions for the dataset, you must contact the designer to obtain the dataset.
