Analyse Power BI Data in Excel

A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.

Requirements

  • Desktop versions of Microsoft Excel 2007 and later
  • Download and install Power BI Publisher for Excel
  • Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.

Enable Power BI Publisher for Excel

Connect to and Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)

Previously we could analyse Power BI data in Excel directly from Power BI service by:

  • Log in to Power BI Service
  • Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”

Analyse Power BI Data in Excel from Power BI Service

  • Clicking ellipsis button of a desired report and clicking “Analyse in Excel”

Analyse Power BI Reports in Excel from Power BI Service

  • Doing either way, it downloads an “odc” file that could be opened in Excel.

Analyse Power BI Data in Excel from Power BI Service Enable Data Connection

  • Now you can analyse the data in Excel using pivot tables and pivot charts.

Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (Directly From Excel)

With the new Power BI Publisher add-in for Excel we can now easily connect to a Power BI service account and analyse the reports and datasets directly from Excel:

Analyse Power BI Data in Excel from Excel

  • If you haven’t already logged into your Power BI service you will prompt to login. Enter your email address associated with Power BI service account and click “Sign In”

Power BI Publisher for Excel Connect to Power BI

  • Select a workspace, report or dataset then click connect

Power BI Publisher for Excel Connect to Data in Power BI

  • Now you can analyse the data in Excel without needing to download “odc” connection file like what we used to when we wanted to analyse a report or dataset from Power BI service.

Analyse Power BI Data in Excel

  • If you do not have any data in your Power BI service, the published add-in detects that and offers you to create sample data in Power BI. Click “Connect to Sample”

Create Sample Data in Power BI From Excel

Analyse Power BI Sample Data in Excel

  • If you login to your Power BI service you’ll see the sample

Power BI Sample Data

Switching Between Power BI Accounts

If you have different Power BI service account and you’d like to switch between your accounts and analyse the different accounts from Excel just click “Profile” from “Power BI” tab from the Excel ribbon then click “Sign Out”.

Power BI Publisher for Excel Switching Power BI Accounts

Now click “Sign In” and use your other Power BI service account credentials.

Power BI Publisher for Excel Switching Power BI Accounts

Note: To make this work you have to close Excel and reopen it while you switched to another account from the Power BI Publisher otherwise you’ll get the following error:

“Error Code: 403 (Forbidden) GroupNotAccessible”

Error Code: 403 (Forbidden) GroupNotAccessible

Error Code: 403 (Forbidden) GroupNotAccessible

Considerations

There are a few limitations applied to the current version of Power BI Publisher for Excel. In this section I briefly discuss those limitations.

Shared data is NOT available in Power BI Publisher for Excel

The data that is shared with you in Power BI is not available in Power BI Publisher for Excel. To check this limitation, open your web browser and login to a Power BI service account and make sure you have some data shared with you. The following screenshot shows an account that has no datasets and reports, but, it has a shared dashboard.

Shared data is NOT available in Power BI Publisher for Excel

Now connect to the same account from Power BI Publisher from Excel and you’ll the shared data is NOT available.

Shared data is NOT available in Power BI Publisher for Excel

Power BI Workspace Groups

If your Power BI account is a member of a workspace group that shared dashboards, reports and datasets with its members, and if “Edit Power BI Content” is not granted to your account, then you won’t be able to see reports or datasets in Power BI Publisher. You also cannot see the workspace that you don’t have “Can Edit Power BI Content” permission on in the Power BI Publisher.

The following screenshot shows a Power BI account that is a member of two workspace groups named “Sales (Read only)” and “Sales (Edit enabled)”. The account doesn’t have “Can Edit Power BI Content” rights on the “Sales (Read only)” workspace. But, it has the right to edit Power BI content on “Sales (Edit enabled)”. As you see there is a report and also a dashboard shared with this account under “Sales (Read only)” workspace.

Power BI Workspace Groups

When I connect to that account from Power BI Publisher, I cannot see the data associated with the “Sales (Read only)” workspace. Indeed the “Sales (Read only)” workspace is not even in the list.

Connect to Power BI Workspace Groups from Power BI Publisher for Excel

However, I can connect to the “Sales (Edit enabled)” workspace and analyse it in Excel.

Analyse Power BI Data in Excel

Analysis Services (SSAS) On-premises

If you want to analyse a Power BI dataset or report that originates from an on-premises SSAS Multidimensional or Tabular model and the dataset in Power BI uses “Connect Live” to access the data, then you should be aware that the Power BI Publisher for Excel connects to SSAS through your local network. This means that any user trying to analyse an SSAS dataset in “Connect Live” mode must be connected to your local network. The user should be also authenticated on your SSAS server.

This is what you see if you try to connect to an SSAS dataset or report from Power BI Publisher for Excel:

Analyse SSAS On-premises Using Power BI Publisher for Excel

Analyse SSAS On-premises Using Power BI Publisher for Excel

UPDATE

Admin Settings for On-premises Analysis Services Data Controlling “Analyse in Excel”

If you are an Office365 Global Admin and you want to control whether or not the users in your organisation can analyse your on-premises SSAS data in Excel, with the July 2016 update you can do that. This option is enabled by default so if you want to disable it:

  • Log into Power BI Service
  • Click “Settings” then click “Admin Portal”

Power BI Admin Portal

  • Click “Tenant settings”
  • Switch off “Allow users to Analyse in Excel with on-premises datasets” then click “Apply”

Power BI Admin Portal Tenant Settings

Now, if you click “Analyse in Excel” in Power BI Service you get the following message:

Analyse in Excel is disabled

You won’t be able to connect to a dataset/report and Analyse in Excel from “Power BI Publisher for Excel”.

Analyse in Excel is disabled

Leave a Reply