Image: Vadim/Adobe Stock For the Microsoft Power BI user, importing Microsoft Excel information is a common job. When in Power BI, you evaluate and report on that information. Sometimes, you may require to export a Power BI dataset to Excel. Maybe a colleague wishes to further examine the data, or you want to analyze it a bit more; the procedure of exporting to Excel can be easy or impossible depending upon your status. In this tutorial, I’ll reveal you several methods to export Microsoft Power BI datasets to the.xlsx Excel format. We’ll begin with the simplest technique and overcome the more intricate techniques. The conversation will include directions for the dataset’s owner and completion users due to the fact that the export experience will be various. SEE: Hiring package: Microsoft Power BI developer(TechRepublic Premium)I’m using Microsoft Power BI online and desktop with Microsoft 365 desktop ona Windows 10 64-bit system; Power BI does not support the earlier.xls Excel format. For your convenience, you can follow along by downloading the demonstration.xlsx Excel. Dive to: Who can export Power BI datasets?
Must-read Windows protection Power BI is an organizational tool, so you may believe that anyone with a link to a dashboard can export the underlying dataset, but that will not always hold true. 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, audiences– also called consumers and end users– can’t export data through a control panel or
report. SEE: The Complete Microsoft
Power BI Super Package (TechRepublic
Academy) Report designers, who are the owners of the dataset, control how end users can export data by setting one of these permissions: End users can export summarized information. End users can export both summed up data and the underlying dataset. End users can’t export any information. As an end user, if you can’t export the data, you can ask for the information by seeing the contact details under the report title, as shown in Figure A. Figure A Contact the dashboard owner to request the data. 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. Complete any additional info and
- send it as you usually 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. Merely 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 select Copy Table. Doing so copies the dataset to the Clipboard. From there, open a blank Excel workbook and paste the dataset into a sheet. You can now send it off to your colleague. Figure B< img src ="https://www.techrepublic.com/wp-content/uploads/2022/06/PBIToExcel_B-770x402.jpg"alt=" "width="770" height =" 402"/ >
Copy the table and after that paste the data
into an Excel workbook. 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; what you’ll get depends upon the specific
authorizations set by the designer or Power BI’s default settings. Figure C shows 2 visualizations based on a report named Sales by Month and Area report. That report, in turn, is based upon a dataset– it’s the dataset you seek. Figure C Pick the Export Data choice. If the owner set export consents in Power BI desktop when creating the report, you must be able to export the dataset.
The procedure is simple, but it may not return the complete dataset. To export, do the following: Hover your mouse over the tile or visualization in concern. You’ll see an ellipsis (… )in the
upper-right corner. If you don’t, click above the visualization. From the ellipsis dropdown, choose Export Data(Figure C). In the resulting dialog, select the.xlsx format from the File Format dropdown(Figure D
). You can also pick to export a.csv
file. Notification also that Power BI restricts this particular export to summarized information. This means you can download the information seen in the visual, however not the total dataset. If you want the complete dataset
- , you can stop here and call the owner. You may also see a sensitivity warning, particularly if your company has rules in place about how to deal with sensitive data. To export, click Export. When triggered by your internet browser, click Open. Figure D Select to export to Excel or
- a.csv file. The open file will be an Excel.xlsx file. It’s not likely that the summed up data will be what you want at this point. Examine Action 4 above before quiting. 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 fortunate sufficient to grab all the information, you can use Excel to work with this data as you would any
- other information.
- How to set export consents in Power BI Desktop As the designer
, you might desire
to enable exports. If so, you’ll need Power BI Desktop to do this. To start, click the File menu and choose Alternatives and Settings. In the resulting window, select Alternatives. In the left pane, in the Present File section, select Report Settings. To the right, choose the appropriate export settings, as displayed in Figure E, and click OK. As you can see, this report allows end users to download the whole dataset, not only the summarized version. Figure E Enable end users to export the dataset.
Once the consent is set to permit the export of the dataset, the consumer will see the options displayed 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
With the best approvals, a customer can download the underlying dataset. Figure G< img src =" https://www.techrepublic.com/wp-content/uploads/2022/06/PBIToExcel_G.jpg?x93735 "alt =""width= "363"height ="586"/ > Power BI downloads the dataset in the.xlsx Microsoft Excel format to 
the local system. How to export from a Power BI control panel A Power BI control panel is the last
product that the designer wants end users to see. Exporting from a control panel is similar to the above procedure for reports, but unless consents allow for exporting, you will not get anything. The ellipsis dropdown will have more choices, and the list may restrict you to exporting just as a.csv file. If you have the ability 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 Information. Then, pick 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 approvals set by the owner. If you’re the owner and you have Power BI Desktop, your export task is easy. If you are a consumer and the designer did
