Starting in Excel
Our first example will begin the process in Excel and use a Power Query connector to import data from a Power BI dataset.
Featured Course
Master Excel Power Query – Beginner to Pro
The popular Power BI report everyone at the office is raving about looks like this.
The name of the report is in the upper-left corner of the report.
We’ll use the name of the report to locate it when we begin the connection process.
What makes this process fantastic is that we will be able to pick and choose data from different tables, combining the information into a table that suits our specific needs. The data will also be connected to Power BI, so receiving the latest data is as easy as “right-click, refresh”.
Insert Table from Power BI
Starting with Excel, click Data (tab) – Get & Transform Data (group) – Get Data – From Power Platform – From Power BI.
A pane will open on the right of the screen that displays a list of all Power BI datasets that have been promoted or endorsed by your organization or that you have recently accessed. You will need Build permissions (full edit rights) to the dataset to connect to it from Excel.
If you don’t see the dataset you are trying to connect to, you can search for it by name – even partial, as well as by owner, by workspace where it is hosted, name of the Power BI report(s) created from the dataset, or any of its tables.
At the bottom of the advertised Power BI report, we can insert the data in the form of a Pivot Table or a regular table.
Since many Excel users prefer tables, we’ll go with the “Insert Table” option for this demonstration.
NOTE: It is a brand new functionality, currently rolling out to Excel for Microsoft 365.
A very nice interface appears that allows us to select entire tables, or selected columns from only the table we are interested in.
This provides maximum customization for selecting only the information you are interested in without having to deal with the complexities of Data Models.
Reordering Power BI Selected Fields
The order that items are selected from the DATA panel is the order they will appear “left to right” in the table.
If you wish to have the columns in a different order than they were selected, you can use the BUILD panel to drag and drop the order of table columns.
Filtering Power BI Data
If you don’t want every record from the Data Model, you can apply filters to selected fields or add fields that will serve only to filter the selected data.
Opening the FILTERS panel will display all data-based fields (i.e., not Measures) and provide traditional checkbox filters.
When finished selecting, arranging, and filtering, click Insert Table to send the results back to an Excel Table.
The output takes the form of a connected table that can be updated using any common refresh mechanism, like “right-click on table -> refresh”.
The delivered data can be enhanced by adding traditional Excel formulas, Conditional Formatting, charts, pivot tables, etc.
NOTE: If you modify the headers, they will revert to the original names after you refresh the table.
Checking the Output’s Connection Properties
If you need to examine the connection properties, or you want to set the refresh on a timed schedule, click in the data and select Data (tab) – Queries & Connections.
The link to the Power BI dataset will be listed under the Connections section of the “Queries & Connections” panel.
A right-click – Properties will display the Connection Properties dialog box for the selected connection.
Here, you can set timers for automated refreshes, modify the underlying query definition, and see all the locations where the output is being used.
Insert a Pivot Table from Power BI
As a slight variation of the previous method, instead of creating a table from selected fields, we can send the entire Power BI Data Model to a Pivot Table.
As before when in Excel, click Data (tab) – Get & Transform Data (group) – Get Data – From Power Platform – From Power BI or you can use the more direct approach of Insert (tab) – Tables (group) – PivotTable – From Power BI.
Here, instead of selecting Insert Table, select Insert PivotTable.
This sends the entire Data Model into an Excel Pivot Cache where you can now build a Pivot Table in the normal way.
Add fields from the model to the Rows, Columns, Filters, and Values field wells of the Pivot Table.
A great benefit of using this method is that any formatting that has been applied to the Power BI data will carry through to the Pivot Table.
Featured Course
Fast Track to Power BI
Starting in Power BI Service
Analyze in Excel
If you happen to be in a Power BI report, and you want to build an Excel Pivot Table using the report’s Data Model, you can bypass many of the initial steps in the previous example by using the following approach.
When viewing a Power BI report in a web browser, you can click Export in the menu bar and choose Analyze in Excel.
This will send the Power BI Data Model to the online Web version of Excel for you to build your Pivot Table. At the same time, the workbook is automatically saved to your OneDrive for Business.
If you are more comfortable using the local version of Excel instead of the Web version, you can click the Editing button in the top menu and select “Open in Desktop App”.
Now you can work in traditional Excel and leverage all the functionality that the Desktop version provides.
As of May 2023, dates and implicit measures from Power BI datasets are properly supported in Excel pivot tables. You can drag any numerical field into the Values well and select the desired aggregation type, whether you want sum, average, or count. You don’t have to rely on explicit measures (created in the data model using DAX).
One advantage of explicit measures is that they come already properly formatted. Not to mention, they allow for more complex calculations.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Export Data from Power BI with Live Connection
Our last option in this post is exporting a specific visualization’s data to a table in Excel.
This is useful when you don’t want the entire Data Model’s data, but rather only the data used to drive a specific visual.
We have the option of exporting the data as static information or as a live connection so updates can be received in Excel.
When examining a visual in Power BI on the Web, select the More Options button (ellipsis) and select Export Data.
We’re given the option to export the summarized data.
You can also choose to export the data in either Excel or comma-delimited format. Be aware, that the comma-delimited and disconnected version of Excel will reduce the maximum number of exportable rows in the data.
Clicking Export will download the data to a file on your local computer. When opened in Excel, the data appears as a table that can be formatted as desired. If you chose the live-connected option, your table will be refreshable.
NOTE: To interact with the data, you will need to Enable Editing, as Excel tries to protect you from data coming from outside, untrusted sources.
Also, you may need to click Enable Content to allow for the linked data to be refreshed.
The data will appear on a sheet named “Export”, while another sheet named “Export Details” will display a list of any applied filters that may have reduced the rows in the exported data.
Working with a Live-connected Table
The data may include not just the fields you actually see displayed in the Power BI visual, but also any underlying fields, for example those used to filter the visual.
You can apply conditional (and regular) formatting, as well as perform any calculations using Excel formulas.
When new data becomes available, all you need to do is run any of the typical refresh actions. Any formatting you applied will remain, even after refresh.
Featured Course
Black Belt Excel Package
Prerequisites
- Your Power BI administrator has to enable the following in the Tenant Settings:
- Users can work with Power BI datasets in Excel using a live connection;
- Allow XMLA endpoints and Analyze in Excel with on-premises datasets.
- You must have Build Permissions (full edit rights) to the Power BI dataset (or at least a Contributor role in the Power BI workspace containing the dataset).
- You need a Power BI license that is suitable for the workspace (Pro license for Pro workspace, Premium per User license for PPU workspace, free license only if workspace in Premium capacity).
*We cover Roles and Permissions as well as licenses in the Fast Track to Power BI course.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
