How to connect to Power BI dataset with Excel

Here is one of the easiest ways to connect to the Power BI dataset with Excel to use it in PivotTable. First of all, you or other users should have access to the dataset. If you are the owner of the dataset, then that is not a problem but might be necessary for others.

There is another thing to keep in mind. All the calculations in Excel PivotTable come from DAX measures. When a report and corresponding dataset are published, then all the measures come from the report. If you don’t have any DAX measures, then there is nothing much to do in Excel.

Connect to Power BI dataset with Excel

1. Go to the Power BI service and find the dataset.

2. Go to the dataset options and choose to Analyze in Excel.

Go to the dataset options and choose to Analyze in Excel.

3. Excel with the live connection to the dataset will be downloaded.

If you have technical issues connecting to Power BI dataset with Excel, then look at it here.

You will see the results through Excel PivotTable and calculations from DAX measures in the Power BI report. You can view detailed data as usual – by clicking on the calculation in PivotTable. There is a limitation that you can change in connection properties.

increase Excel PivotTable retrievable rows

Other users that have access to the same dataset can use this Excel, and you can share it. You can grant those permissions in the dataset options like this.

Sometimes after inactivity in Excel, there might be an error: session ID cannot be found.
Don’t worry about that. Usually, by repeating the last action, it will continue to run properly.

Excel error session ID cannot be found

Connect to Power BI dataset directly from Excel

With the appropriate Office version, it is possible to get Power BI datasets directly from Excel. Go to the Data tab, choose Get Data, and if you are logged in Excel with the user that has the right access, there should be an option From Power BI.

connect to Power BI dataset directly from Excel

Accessing the Power BI dataset by inserting PivotTable might be an even better and faster approach.

get Power BI dataset with PivoTable

Earlier, there was an option to use Power BI publisher for Excel, but that is retired.

Here is a summary about connecting to Power BI dataset with Excel

You should have access to the Power BI dataset.
Calculations in Excel PivotTable come from DAX measures in the report related to the Power BI dataset.
Go to the dataset options and choose to Analyze in Excel.
Other users that have access to the same dataset can use this Excel.

Here is something that might be useful for you: 4 ways how to organize DAX measures in Power BI.

Posted

July 8, 2021

Excel, Power BI

Comments

One response to “How to connect to Power BI dataset with Excel”

Marta
October 3, 2023
Hi,
I was trying to connect to PBI directly from Excel.
I could see only 15 or so datasets, not all the ones I have access to, neither could I search them in the search box.
Next time I tried again and I could see a dataset that I was looking for last time, but another one ‘disappeared’. Still not all of them are listed there.
When I enter via PowerBI service, I do have access to the dataset that I’m looking for.
Any ideas?
Could it be a matter of changes newly implemented in the datasets and synchro?
Reply