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

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.
Learn More
Power Query Course cover

The popular Power BI report everyone at the office is raving about looks like this.

A report page in Power BI Service.
If you would like to learn how to create this report from scratch, check out our Fast Track to Power BI course.

The name of the report is in the upper-left corner of the report.

Power BI report name

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.

Excel menu to get data 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.

Power BI Datasets pane in 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.

Power BI report discovered in Excel via search bar on the Power BI Datasets pane

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.

Insert Pivot Table and Insert Table options in the Power BI datasets pane in Excel.

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.

List of tables from Power BI data model

This provides maximum customization for selecting only the information you are interested in without having to deal with the complexities of Data Models.

Preview of selected Power BI columns to insert as Excel table

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.

Build panel on the Create Table interface when importing data to Excel via Power BI connector

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.

Filters panel on the Create Table interface when importing data to Excel via Power BI connector

When finished selecting, arranging, and filtering, click Insert Table to send the results back to an Excel Table.

Insert Table button
NOTE: After you Insert the table, you can no longer edit it using the “Create Table” interface.

The output takes the form of a connected table that can be updated using any common refresh mechanism, like “right-click on table -> refresh”.

Refresh data in Excel using a right-click method

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.

Accessing Queries and Connections from Excel ribbon

The link to the Power BI dataset will be listed under the Connections section of the “Queries & Connections” panel.

Excel list of external data connections on the Queries & Connections pane

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.

Connection Properties dialog box

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.

PivotTable from Power BI option in the Insert ribbon in the Excel menu

Here, instead of selecting Insert Table, select Insert PivotTable.

Insert PivotTable option when connecting to Power BI

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.

Finished Pivot Table in Excel with PivotTable fields to the right

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.

Formatted aggregations from DAX measures

Featured Course

Fast Track to Power BI

Stay ahead of the game in 2024. Get access to our best-selling Power BI course now and become a highly sought-after Power BI professional. This course gets you started in Power BI – Fast!
Learn More
Power BI Course image

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.

Export feature in Power BI Service with Analyze in Excel highlighted.

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.

Building an Excel Pivot Table from a Power BI data model

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”.

Open the Desktop version of Excel from the Web version of Excel

Now you can work in traditional Excel and leverage all the functionality that the Desktop version provides.

Building a Pivot Table from Power BI data

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)

Transforming data into meaningful reports can feel like being in an Olympic event. It is hard! Join the course and learn how to use Excel’s Power Pivot and Data Modelling tools to make reporting easy and fast.
Learn More
Power Pivot DAX course cover

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.

Export Data option in a Power BI report

We’re given the option to export the summarized data.

Power BI export data options dialog box with Summarized data highlighted and File format .xlsx (Excel) with live connection (500,000 row max) selected.

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.

Power BI export options for File format, including xlsx with live connection (500,000 row max), static xlsx (150,000 row max) and static csv (30,000 row max)

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. 

Protected View warning in Excel (file from the Internet) with Enable Editing button highlighted

Also, you may need to click Enable Content to allow for the linked data to be refreshed.

Security Warning in Excel - External Data Connections have been disabled - with Enable Content button highlighted.

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.

Power BI Export details sheet in Excel

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.

Formatted table in Excel from exported Power BI data. Conditional Formatting (color scale) applied to the Total Sales column.

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

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Prerequisites

  1. 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.
  2. 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).
  3. 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.