Power BI and Excel; More than just an Integration

Power BI and Excel are longtime friends of each other, not only because Power BI components are coming from add-ins that were introduced in Excel, but also because of the way that these two tools interact with each other from the Power BI Service. This post is not about using Power Query or Power Pivot components in Excel. This post is about the interaction between Excel and Power BI through the service.

Power BI and Excel integration through the service gives the user the ability to use Excel as their slicing and dicing tool while connected to a live Power BI dataset. On the other hand, you can pin a range of cells from an Excel document into Power BI dashboard. Excel files also can be uploaded to the Workbook tab of the Power BI service. In this post, you will learn the below ways that Excel and Power BI interact with each other through Power BI Service;

  • Analyze in Excel
  • Power BI Publisher for Excel
  • Upload an Excel workbook into Power BI Service
  • Import Excel into Power BI Desktop

Analyze in Excel

In every company, you will find some users with very good experience and skillset of Excel. Excel users can still use Excel to connect to the Power BI dataset, and use Excel features such as PivotTable and PivotChart to slice and dice the data. The connection to the Power BI dataset would be a live connection, and it means whenever users refresh the Excel file they will get the most up-to-date data from the Power BI service. Now let’s see how Analyze in Excel works in action.

Analyze in Excel from Power BI Service

You can start looking at Analyze in Excel from a Power BI Service report. Log in to Power BI service, and open of the Power BI reports. In the top right-hand side, after clicking on more options, you will find Analyze in Excel.

You can also open Analyze in Excel from the workspace directly without opening the report. Simply click on the Excel icon beside the report’s name.

If this is the first time that you are using this feature on your machine, you may be asked to install a plugin for Office connection add-in.

After downloading it (which shouldn’t take long), you can install the add-in. The installation is for Microsoft Analysis Services OLE DB Provider for Excel.

Follow the instructions set up. The reason that Analysis Services OLE DB provider is required for Analyze in Excel to work is that Power BI datasets are hosted in an Azure Analysis Services instance.

After successful setup, try Analyze in Excel again, and this time you can choose; I’ve already installed it.

The Analyze in Excel option in Power BI service will download an ODC file (ODC stands for Office Data Connection). ODC file can be opened with Excel.

When you open the file in Excel, you will be asked to enable the connection. The reason for the question is that you are connecting to a data source in the cloud.

If this is the first time you are opening this, you may be asked to log in. Use the same Power BI account username and password that you had access to the report from it. After successful sign in, you should see a PivotTable with data tables and fields fetched from Power BI model.

Drag data fields into the slicing and dicing area (right under the fields pane), and you will see a result coming up in PivotTable. This result is fetched live from Power BI model in the Power BI service.

Implicit Measures won’t work in Excel

Implicit measures are a measure that Power BI create them automatically. Power BI automatically applies auto summarization on numeric fields (that haven’t been part of a relationship). Power BI behind the scene is creating a measure for those fields; these measures are called Implicit measures. These are a measure that you can see a small Sum or Sigma icon beside their name in the Power BI Desktop.

The implicit measure cannot be used in the Analyze in Excel feature; if you try to drag them in PivotTable, instead of seeing the aggregation or measure result, you will see individual values.

However, if you create explicit measures (which are DAX measures created by you), similar to the screenshot below;

Then you can use them in PivotTable as a normal measure and see the correct result.

If you consider some users to use Excel as their front-end tool to connect to Power BI Models, You have to consider creating the Explicit measure.

Excel is Connected Live to the Power BI Model in the Service

The wonderful thing about the Excel connection to the Power BI Service is that the connection is Live. Live connection means Excel fetches the data directly from the dataset in the Power BI service. Anytime you refresh the Excel file; you get the most up-to-date data from the service. This feature is completely different from Export to Excel. Export to Excel option that you see on visuals in the Power BI service is only downloading data offline, however, Analyze in Excel is an online and live connection to the dataset.

You can check the connection properties in the Data tab, under Connections, Properties section.

The Connection properties will have an Azure address in it with the ID of the dataset in the Power BI service. You can even use this connection in any other excel file to connect to the same dataset.

Power BI Publisher for Excel

There is an add-in for Power BI integration with Excel, named Power BI Publisher for Excel. Power BI Publisher for Excel is a free add-in, which enables the two ways integration of Excel and Power BI. With Power BI Publisher for Excel, you can pin a range of cells into a Power BI dashboard, this range of cells will be updated on a scheduled basis. You can also use the ability to connect to a data model in Power BI and start slicing and dicing it (same as Analyze in Excel). Steps below shows how Power BI Publisher for Excel works.

To use the Power BI Publisher for Excel, download the add-in for free from here:


Then continue setup steps to install the Power BI Publisher for Excel

After installation, you can open a blank Excel document, and you will see a Power BI tab there.

The Power BI tab has two main action items; to connect to a model in Power BI, or to pin an item into Power BI service.

Connect to Data

Connecting to a model in Power BI service is very similar to Analyze in Excel. When you click on Connect to Data, you will be asked to log in to your Power BI account information.

You then will be asked to choose a workspace, and then a dataset or report to connect to it.

Then the dataset will be accessible through a PivotChart, exactly similar to the way that Analyze in Excel works. In fact, there is no difference between Analyze in Excel and the Connect to Data in Power BI Publisher for Excel.

Pin to a Power BI Dashboard

Another feature in Power BI Publisher for Excel (which is not available in Analyze in Excel), is that you can select a range of cells in Excel, and Pin it to a Power BI Dashboard.

You can either create a new dashboard or pin it to an existing dashboard. The range of cells then will be part of your dashboard. However, the excel range will not be interactive or editable. It will be more like a screenshot.

The Excel range, however, will be updated in Excel. You can see the configuration in Pin Manager menu option of the Power BI Publisher for Excel.

To get the pinned range of cells up-to-date, you need to save the Excel file.

Power BI Publisher for Excel enables two-way integration between Power BI and Excel. You can connect to a Power BI model from Excel and slice and dice data with it, or you can pin part of an excel spreadsheet into a Power BI dashboard.

Upload an Excel workbook into Power BI Service

It is not that much common to upload an Excel workbook into a service; However, if you want to have a single portal to share Excel workbooks, as well as the Power BI, reports, this option would be a useful option to leverage. An Excel workbook uploaded into the service can be opened and edited with Excel online or local version of Excel. Here is an example of uploading an Excel workbook into the service.

In Power BI Service, click on Get Data.

Select Files as a Data Source and then choose an Excel file.

After selecting the file, then choose to Upload your Excel file into Power BI.

The Excel file will be then visible in the Workbooks tab of Power BI service.

You can now click on the Excel file and edit it with Excel online.

Having the Excel workbook uploaded into the Power BI service gives you the ability to interact with it with Excel Online, Edit it and change it while it is hosted in the same portal that Power BI reports are hosted there (Power BI service).

Import Excel into Power BI Desktop

We cannot talk about all integration options between Excel and Power BI without mentioning the most important developer option. You can import an Excel model which includes a Power Pivot model into Power BI. The Import into Power BI doesn’t mean Get Data from Excel, it means the entire model including all the tables, relationships, and calculations will be imported into a Power BI report. The scenario above is a very handy option when you have an existing Power Pivot model in Excel.

Importing an Excel workbook has a very easy and straightforward process. The only important note in this process is that your Excel file should not be password protected.


In this post, you’ve learned four methods that Excel and Power BI work together. Each of the modes of integration will give you some features. Analyze in Excel will give you the ability to slice and dice the data model of Power BI service easily from Excel through PivotTable and PivotChart. Power BI Publisher for Excel has one step, also, to Analyze in Excel, which is pinning part of an Excel content into a Power BI dashboard and updating it. Both of features mentioned above will be highly well received by your business users who are good with Excel as a front-end tool.

You can upload your Excel file into the workbooks section of Power BI service; this feature will give users ability to access the Excel content from the same portal that Power BI reports are hosted, and Edit the content with Excel Online. Last but not least, you can import an entire Excel Power Pivot model with all tables, relationships, and calculations into a Power BI report instead of re-creating it.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

17 thoughts on “Power BI and Excel; More than just an Integration

  • Great article. Are any of these features available for the Power BI on-premise report server or are they exclusive to the cloud service only?

    • Hi Jeff.
      These features at the moment is for service only.
      in Power BI report server; the approach is that if you want to use the integration; then you can use SSAS live connection, and you will be able to use Excel to connect to it.


  • Excellent article. One use I can see of pinning screenshots into dashboards could be to add comments. Just a thought, I haven’t actually tried it out yet. Interested in knowing your thoughts.

  • Is there a way to rapidly create measure so that PowerBI and Excel can communicate properly. I find it frustrating that this Microsoft solution does not allow Excel to recognize Implicit measure in Excel. After adding explicit measures to my data model, I’m left with duplication and quite frankly, a mess. There must be a better way.

    • unfortunately not. But when the XMLA endpoint becomes available for Power BI, then tools like Power BI Helper would be able to interact with the model and possibly create measures dynamically.

  • Hi! Is there a way to use “Analyze in Excel” with a Table (Excel flat table) connected to the PBI, instead of a pivot table?

    • Hi Marnique
      What do you want to do with a flat table? I believe you can do whatever you want with PivotTable and build it in flay layout


  • Reza,
    can we extract Initial Catalog (this part in odc string) from the web to change Propeties in excel in manual way?

  • Hi –

    Thanks for the overview of Excel / Power BI integration options. I’ve found your blog to be a great reference as we roll out Power BI in our org!

    We have a few users that defer to the Analyze in Excel option. We seem to be having a sporadic issue, however, in that one particular dimension field will hang up when added to a pivot table filter and expanded in the drop down (much like a slicer would behave in the service). This happens maybe 5% of the time and is usually fixed with a dataset refresh. Further, the issue seems to be limited to Excel and not the Power BI service. Excel will show “Running OLAP query…” in the bottom right when this is happening, and pressing ESC will cancel the operation. The dataset is fairly small and narrow and this field only has about a half dozen distinct values (so it’s not producing a list of thousands or millions in the drop down). I’m wondering if you know of ways to troubleshoot this (capture the OLAP query trying to execute?) and/or whether you’ve experienced something like this before and what the fix might be?

    Apologies for the support question… I’ve just had a tough time finding much info on this issue. Appreciate any insight!

      • Hi Reza,
        I could email the file, but I’m not sure what good that will do since it requires connection to the dataset and not something available outside of our tenant. I also can’t seem to find your email address on the site. If possible, would you mind sending me an email and we can discuss options from there?

        I appreciate the assistance!

  • Thanks for this great article.
    I would have one question, in section “Excel is Connected Live to the Power BI Model in the Service” you mentioned that there is live connection between Excel and Analysis Service model of PBI Service.
    Do you know (and can give me a hint how) how to connect to this AS model via Power BI Desktop, with Import mode, and defining this connection manualy ?

Leave a Reply

%d bloggers like this: