Edit

Share via

Connect Excel to Power BI semantic models

Microsoft Excel is arguably the most common data analysis tool for information workers and organizations across the globe. Part of Excel’s appeal is that you can start your data journey in a few clicks, with quick calculations, data visualizations, and ad-hoc analysis.

You can discover, connect to, and explore centralized Power BI data inside the spreadsheet grid of Microsoft Excel. The versatility of Excel and the unified governance and security standards of Power BI ensures you're using the right data to make better decisions that support business goals and fosters an enhanced data culture.

Screenshot of Power BI semantic models in Excel.

Access Power BI semantic models in Excel

When you access Power BI semantic models in Excel, the Excel file automatically inherits properties, like endorsement labels and sensitivity labels from Microsoft Purview Information Protection, to help make sure that the data stays secure. Semantic models tagged with the labels Certified or Promoted are likely to be better quality and more authoritative than semantic models without a label.

Benefits of using Power BI semantic models in Excel

Using Power BI semantic models in Excel allows you to leverage the powerful data analysis capabilities of Excel while ensuring data governance and security provided by Power BI. This integration helps you make better decisions and supports business goals by using accurate and authoritative data.

Connect to Power BI semantic models in Excel by starting in the Power BI service or in Excel for the web or the Excel desktop app.

Connectivity improvements

Excel with Build number 16.0.18129.x or higher, using MSOLAP version 160.139.29 or higher, can connect to Power BI semantic models through the XMLA endpoint, by default. Older Excel versions connect by default through a legacy endpoint for Analyze in Excel (AIXL).

Note that even the most recent versions of Excel and Power BI Desktop in Live Connect mode might fall back to the legacy AIXL endpoint if Read access to your XMLA endpoint is not enabled. Given the broad adoption of the XMLA endpoint, and its improved performance, functionality and reliability in comparison to the AIXL endpoint, we strongly recommend that you enable at least Read access to your XMLA endpoint and update to the latest Excel version for the following reasons:

  • The XMLA endpoint provides better performance than the AIXL endpoint.
  • The XMLA endpoint is more resilient than the AIXL endpoint. For example, long-running queries may fail with AIXL but are supported over XMLA.
  • DAX Info functions are available over XMLA endpoint while AIXL does not support the DAX Info functions.

To check the Build number of your Excel version, click on File, select Account, and then click on the About Excel button. The About Excel dialog box displays version information together with the Build number above the licensing information, such as (Version 2506 Build 16.0.18925.20022).

Prerequisites

Here are things to keep in mind when you connect to Power BI semantic models in Excel:

  • Your organization’s tenant administrator must enable the Users can work with Power BI semantic models in Excel using a live connection tenant setting. Learn more about the Excel live connection tenant setting in the admin portal documentation.
  • For on-premises datasets, your organization’s tenant administrator also must enable the Allow XMLA endpoints and Analyze in Excel with on-premises datasets tenant setting. Learn more about the Allow XMLA endpoints tenant setting in the admin portal documentation.
  • You must have Build permission to the Power BI semantic model or have at least a Contributor role in the Power BI workspace containing your semantic model.
  • When connecting through the XMLA endpoint to a composite Power BI semantic model that relies on other semantic models downstream, you must have Build permission or Contributor rights to all the semantic models. The AIXL endpoint, on the other hand, enforces Build permissions or Contributor rights only on the composite model.
  • You must have a Power BI license to connect Excel to Power BI semantic models. Learn more about Power BI licenses.
  • You can use Excel workbooks containing refreshable Power BI data in both Excel Desktop and Excel for the web.

Related content