For the Microsoft Energy BI report designer, importing Microsoft Excel knowledge is a standard activity. As soon as in Energy BI, you analyze and report on that knowledge. Often, you may must export a Energy BI dataset to Excel. Maybe a colleague desires to additional analyze the info, otherwise you need to analyze it a bit extra; the method of exporting to Excel will be simple or unimaginable relying in your standing.
On this tutorial, I’ll present you many methods to export Microsoft Energy BI datasets to the .xlsx Excel format. We’ll begin with the simplest technique and work by way of the extra advanced strategies. The dialogue will embrace directions for the dataset’s proprietor and the tip customers, as a result of the export expertise will likely be totally different.
SEE: Discover TechRepublic Premium’s Microsoft Energy BI Hiring Equipment.
I’m utilizing Microsoft Energy BI by way of Microsoft Cloth on a Home windows 11 64-bit system. You don’t want Microsoft Cloth; nonetheless, for those who don’t have it, you may discover delicate variations within the interface.
Bounce to:
Who can export Energy BI datasets?
Energy BI is an organizational instrument, so that you may suppose that anybody with a hyperlink to a dashboard can export the underlying dataset. However that gained’t at all times be the case. By default, Energy BI restricts exporting the underlying dataset to directors and report designers. Except the proprietor of the dataset applies particular permissions for exporting, viewers — often known as shoppers and finish customers — can’t export knowledge by way of a dashboard or report.
SEE: Right here’s the whole lot you should find out about utilizing Microsoft Energy BI with this bundle from TechRepublic Academy.
Report designers, who’re the homeowners of the dataset, management how finish customers can export knowledge by setting one in every of these permissions:
- Finish customers can export summarized knowledge.
- Finish customers can export each summarized knowledge and the underlying dataset.
- Finish customers can’t export any knowledge.
As an finish consumer, for those who can’t export the info, you may request the info by viewing the contact info beneath the report title (Determine A). To take action, click on the LakeHouse knowledge hub possibility, click on the dataset file to open its particulars, after which copy the proprietor’s electronic mail tackle within the Location column. Should you’re fortunate, the proprietor has the info in an Excel .xlsx or .cvs file and might electronic mail or share it rapidly. Should you’re not utilizing Cloth, search for the proprietor by clicking the report title within the report’s title bar.
Determine A
Designers can do themselves and shoppers a favor by setting a report’s permissions to permit shoppers to export the underlying knowledge after they create a report, until there are safety causes for not doing so.
set export permissions in Energy BI Desktop
Because the designer, you may need to enable exports. If that’s the case, you may set permissions in Energy BI Desktop.
In design mode, click on the File menu and select Choices and settings. Within the ensuing window, choose Choices. Within the left pane, within the Present File part, choose Report settings. To the fitting, choose the suitable export settings, and click on OK (Determine B). As you may see, this report permits finish customers to obtain summarized knowledge.
Determine B
use Analyze in Excel
As a shopper, the quickest and best solution to export the dataset to Excel is to make use of the Analyze in Excel function. Whereas viewing the report, click on Export and select Analyze in Excel from the dropdown (Determine C).
Determine C
This function copies the dataset to Excel for the online for fast and easy accessibility. From there, you may obtain the .xlsx file to your native system. That’s the excellent news. The dangerous information is the inherent construction of the exported knowledge: This function exports the summarized knowledge, actually, as a PivotTable. You don’t get the uncooked dataset.
SEE: Discover these Excel ideas everybody ought to grasp.
Relying in your group’s configuration, Analyze in Excel could not even be out there. Should you’re after the info as a easy dataset somewhat than a PivotTable or this function isn’t out there, you may depend on different choices.
copy a desk in Energy BI Desktop
Should you personal the report file and a shopper asks you for its dataset, however you don’t have the unique .xlsx file, you may create one utilizing Energy BI Desktop. Merely open the report in Energy BI Desktop. To the left are three icons. Click on Knowledge — the one within the center — to see the dataset.
SEE: Discover ways to create a date desk in Energy BI.
Proper-click the desk and select Copy Desk (Determine D). Doing so copies the dataset to the Clipboard. From there, open a clean Excel workbook and paste the dataset right into a sheet. Now you can ship it off to your colleague.
Determine D
export knowledge from a Energy BI report
As a shopper, you’ll should work a bit more durable and export from a report; what you’ll get is determined by the precise permissions set by the designer or Energy BI’s default settings.
Determine E exhibits two visualizations based mostly on a report. That report, in flip, relies on a dataset — it’s the dataset you’re after.
Determine E
If the proprietor set export permissions in Energy BI Desktop when creating the report, you must have the ability to export the dataset. The method is easy, however remember that it won’t return the complete dataset. To export as a shopper, do the next:
- Hover your mouse over the tile or visualization in query.
- You’ll see an ellipsis (…) within the upper-right nook. Should you don’t, click on above the visualization.
- From the ellipsis dropdown, select Export knowledge (Determine E).
- Within the ensuing dialog, select the .xlsx format from the File Format dropdown (Determine F). You can even select to export a .csv file.
- Discover that Energy BI limits this specific export to summarized knowledge. This implies you may obtain the info seen within the visible, however not the entire dataset. In order for you the entire dataset, you may cease right here and speak to the proprietor. You may also see a sensitivity warning, particularly in case your group has guidelines in place about the way to deal with delicate knowledge.
Determine F
- To export, click on Export.
- When prompted by your browser, click on Open.
The open file will likely be an Excel .xlsx file. It’s unlikely that the summarized knowledge will likely be what you’re after at this level. Examine Step 4. Should you filtered the visualization earlier than exporting, Energy BI will export the outcomes of that filter. Return to the report, take away any filter and take a look at once more.
Should you’re fortunate sufficient to seize all the info, you should utilize Excel to work with this knowledge as you’ll another knowledge.
export from a Energy BI dashboard
A Energy BI dashboard is the ultimate product that the designer desires finish customers to see. Exporting from a dashboard is just like the above course of for stories, however until permissions enable for exporting, you gained’t get something.
The ellipsis dropdown could have extra choices, and the checklist may restrict you to exporting solely as a .csv file. Should you’re capable of obtain a .csv file, you may open this file in Excel by clicking the Knowledge tab. Within the Get & Remodel Knowledge group, click on Get Knowledge. Then, select From File and at last From Textual content/CSV.
Exporting a Energy BI dataset
Whether or not you may obtain all the dataset from a report or dashboard is determined by your relationship to the info. Should you’re the proprietor and you’ve got Energy BI Desktop, your export activity is easy. If you’re a shopper and the designer didn’t set export permissions for the dataset, you have to contact the designer to get the dataset.
Should you’re fighting Microsoft Energy BI, discover our checklist of the most effective enterprise intelligence instruments to see which software program could also be a greater match for your corporation.
