How to enable Excel Pro’s to use Power BI Datasets
In the steps below, I am going to 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.
Installation 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 in order to access Power BI data.
The first thing I needed to do was to 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 that I have gotten installed.
Once the download was completed, I ensured that Excel was closed and completed the installation.
- I simply accepted all the defaults until the installation was complete.
I then opened Excel and verified that I could now see Power BI in the ribbon
How to connect to a dataset
Next, I will demonstrate how to connect to a dataset.
- I opened Excel and went to the Power BI Ribbon.
I then clicked on Profile and Sign In
I then signed in with my Organizational account
- I verified that I was signed in by clicking on Profile again
Next, I clicked on Connect to Data
- This then brought up all the reports and datasets in my own workspace, as well as other reports and datasets that had been shared with me.
As you can see below these are datasets that were my own
- And if you had a dashboard shared with you, that also means that you have got access to the underlying reports and datasets.
- In order to access datasets shared with you, make sure where it says Select a workspace: it says My Workspace
This is shown below with the Share Icon
- Then click Connect
This will then connect and create the Pivot Tables to the dataset, as you can see with my example below.
Enabling 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.
- The first is via Sharing a dashboard to a particular user.
- The second is 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. As shown below where my user “Pro” can see the dataset that he has got access to via getting an App.
And now when in Excel and the user “Pro” clicks on the Connect to Data, ensures that it is set to “My Workspace”, as well as the data that user “Pro” is connecting to is the Dataset, user “Pro” can connect to the dataset called “Fourmoo Google Analytics”
As 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.