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.

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”

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

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

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

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:

• After you install the Power BI Publisher for Excel, click “Connect to Data” from “Power BI” tab from Excel ribbon

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

• Select a workspace, report or dataset then click connect

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

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

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

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

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

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”

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.

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

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.

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.

However, I can connect to the “Sales (Edit enabled)” workspace and analyse it 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:

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”

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

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

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