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

[GilbertQ]

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.

[PaulPed4]

Hi @GilbertQ ,

That's a pity, would be brilliant if I could extract fields from the dataset into a table and not a PivotTable...

[GilbertQ]

I think you might actually be able to do it with CubeValues

This might help

https://www.excelcampus.com/cubevalue-formulas/

[albertan]

Is this feature only available for Office insiders? I can't seem to see this feature although I downloaded succesfully