Power BI and Excel; More than just an Integration

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

https://powerbi.microsoft.com/en-us/excel-dashboard-publisher

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.

Summary

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

3 Comments

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

      Cheers
      Reza

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

Leave a Reply

Your email address will not be published. Required fields are marked *