Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

GilbertQ

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

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
Anonymous

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

Anonymous

Ignore me, found the answer. 2007

Anonymous

Is Report Server supported or just the cloud service?

Hi @Anonymous

 

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

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

Hi @mkpado

 

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

 

 

Hi @mkpado

 

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

Hi @GilbertQ

 

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

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

Hi there,

 

I only see the option to pull the dataset into Excel in a PivotTable control.

 

Am I able to tie to the dataset and download required fields into a Table in order to provide the report to external clients?

 

Thanks,

Paul

Hi @PaulPed4 

 

Yes that will work.

Hi @GilbertQ ,

 

Thanks, but how do I do that Smiley Happy ?

 

Clearly I'm missing it in what I see in Excel...

Hi @PaulPed4 

 

You can download and install it from here: https://www.microsoft.com/en-us/download/details.aspx?id=50729

Hi @GilbertQ ,

 

I've already downloaded and installed the plug-in, but I only see the ability to build a Pivot Table, not the ability to build a "basic" table of the data to use as an extract to send external users?

 

Thanks,

Paul

Hi @PaulPed4 

 

Yes that is correct when connecting it will always use a Pivot Table to extract the data.