How to enable Excel Pro’s to use Power BI Datasets

by guavaq Super Contributor on ‎12-07-2017 07:42 PM - last edited on ‎12-08-2017 04:50 PM by Power BI Admin MindyJ

In the steps below, I will demonstrate how to access your Power BI Dataset through Excel. This is a great example for people who love Excel and use it as their tool of choice, but the company or organization still wants to have a single version of the data. By leveraging the steps below the data is available in the Power BI Service, as well as from Excel. NOTE: This currently only works on a PC.

 

Install Power BI Publisher for Excel

There are 2 ways to access data in the Power BI Service via Excel. I prefer the method below because it allows for easier connectivity as well as not having to go and download ODC files and then store and open them each time. By using the Power BI Publisher for Excel, people who use Excel will have another item in the ribbon to access Power BI data.

 

Steps:

  • Install Power BI Publisher for Excel. I downloaded it from the following link: Power BI Publisher for Excel.  I chose the Download for Office 64-bit because that is the installation I have installed.
  • Once the download completes, ensure that Excel is closed and the installation is complete.  I simply accept all the defaults until the installation is complete.
  • Open Excel and verify that you can see the Power BI in the ribbon.

Connect to a dataset

Next, I will demonstrate how to connect to a dataset.

  • Open Excel and go to the Power BI Ribbon.
  • Click on Profile and Sign In
  • Sign in with your Organizational account.  I verified that I was signed in by clicking on Profile again.
  • Click Connect to Data
  • This brings up all the reports and datasets in my own workspace, as well as other reports and datasets that had been shared with me. Below you can see my datasets.
  • If you have a dashboard shared with you, you will have access to the underlying reports and datasets.
  • Access the datasets shared with you by selecting My Workspace in the Select a workspace section.  This is shown below with the share Icon.
     
  • Click Connect.  This will then connect and create the PivotTables to the dataset as shown in my example below. 

Enable Access to Dataset via Sharing or App Experience

There are two ways that I am currently aware of where you can grant access to the dataset.

  1. Sharing a dashboard to a particular user.
  2. Create an App from an App Workspace, and then the user gains access once they have got the App.

Completing either of the following above will ensure that when the user clicks on Connect to Data in the Power BI Ribbon they will then be able to see the dataset. In the example below my user "Pro" can see the dataset that he has access to via getting an App.

 

Now when in Excel and the user "Pro" clicks on the Connect to Data, this ensures that it is set to "My Workspace" as well as the data that user "Pro" is connecting to is the Dataset.  The user "Pro" can connect to the dataset called "Fourmoo Google Analytics."

Conclusion

I have demonstrated how to connect to a Power BI Dataset using Excel, as well as how to grant access so that your users can gain access. If there are any questions or advice, please leave it in the comments below.

Comments
by tmckenzie Regular Visitor
on ‎12-09-2017 11:07 AM

What is the lowest version of excel that this works with?

by tmckenzie Regular Visitor
on ‎12-09-2017 11:09 AM

Ignore me, found the answer. 2007

by devdevil85 Frequent Visitor
on ‎01-03-2018 08:03 AM

Is Report Server supported or just the cloud service?

by guavaq Super Contributor
on ‎01-03-2018 02:23 PM

Hi @devdevil85

 

It is only the Power BI Cloud Service that currently supports this.

by mkpado Frequent Visitor
on ‎01-14-2018 11:21 AM

I am trying to connect a dataset from Power BI to Excel utilizing Power BI Publisher. I am drawing an error that says Excel can't access the dataset or report due to non-existant data model (this is inoccrect) or due to permission settings (I own the dataset I am trying to access). Have you run into this error before and how do I resolve? Thank you!

Power BI 2.JPGPower BI 1.JPG

by guavaq Super Contributor
on ‎01-14-2018 05:46 PM

Hi @mkpado

 

I am having the same issue. As soon as I have any more information I will update on this thread.

 

 

by guavaq Super Contributor
Monday

Hi @mkpado

 

I had an update for Office Click-Run today and now the error has been resolved. If you can please check this.

by mkpado Frequent Visitor
Friday

Hi @guavaq

 

Is this an extension or add in? If so can you please send the link where I can install the update? Thank you!

by guavaq Super Contributor
Saturday

Hi @mkpado the link is at thr start of the blog post