You can create PivotTables in Excel that are connected to datasets stored in Power BI with a few clicks. Doing this allows you get the best of both PivotTables and Power BI. Calculate, summarize, and analyze your data with PivotTables from your Power BI datasets.

Notes: PivotTables from Power BI datasets is a Microsoft 365 feature that's rolling out gradually. See When do I get the newest features for more info. To use this feature, you must meet these requirements:

  • To create a PivotTable connected to Power BI, you must be using Excel on a Windows PC and have a Power BI Pro license. It is also recommended that the file be stored in SharePoint or OneDrive.

  • To create or use a PivotTable connected to Power BI, you need to have permission to the underlying dataset. For more details, see the section More about Power BI connected PivotTables.

  • If you're using Excel for the web, you can't create a Power BI connected PivotTables, but if one is shared with you, you can still change fields, use slicers to filter, and refresh data if the file is stored in SharePoint or OneDrive.

How to create PivotTables from a Power BI dataset: 

  1. Select an empty cell in your workbook.

  2. Go to the Insert tab> PivotTable > From Power BI.

    Note: Depending on your organization's IT settings you might see your organization's name included in the button. For example, "From Power BI (Contoso)"

    Insert PivotTable dropdown showing "from Power BI" option.

  3. In the Power BI Datasets pane, choose an enterprise dataset that is available to you. This will create a PivotTable for you in a new spreadsheet of the workbook you're working in.

    Tip: You can also go to the Data tab > Get Data > From Power BI.

    Screenshot of a pane with Power BI datasets.

  4. Once you've created your PivotTable connected to a Power BI dataset, you can:

More about Power BI connected PivotTables

  • To upload an Excel workbook containing Power BI connected PivotTables to the Power BI apps, go to the Power BI portal > under Get data > select Connect.

  • Microsoft Information Protect (MIP) Labels are Microsoft's Security and Compliance offering to protect your data from unintended leaks. Both Excel and Power BI support MIP Labels and can work together to ensure safety of your confidential data in your Power BI datasets. Whenever you create a new connection to a Power BI dataset from the PivotTable functionality described above, the Excel workbook would automatically inherit the label applied on the dataset.

    Caution: MIP Label inheritance does not currently work with other external connections available in Excel.

  • Excel PivotTables connected to Power BI datasets currently do not support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures.

  • Users can connect to datasets in Power BI if they have permission for the underlying dataset. A user could have this permission in several ways, such as having the Member role in the workspace containing the dataset, having a report or dashboard shared to them that uses the dataset, or having Build permission for the dataset, in either a workspace or an app that contains the dataset.

Learn more about datasets and PivotTables

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×