PowerBI datasets in Excel

[tuulia]

Hi! I am trying to "import" Power BI dataset to Excel (Get data -> From Power BI). This dataset is published to PowerBI.com to my workspace. When I open Excel and try to find this dataset I can't find any tabular model-dataset (live connection to model). I find only datasets that connects to SQL Server database. How can I make tabular model-datasets visible in Excel?

[Burningsuit]

Hi @tuulia 

This may be because you don't have read access to the data in Analysis Services. 

From the documentation...

Connecting to Power BI datasets that are built on live connections to Analysis Services is not supported unless the user has read access to the data in Analysis Services in addition to the datasets permissions in Power BI.

See: Troubleshoot connecting Excel to Power BI data - Power BI | Microsoft Docs

Hope this helps

Stuart

[tuulia]

I have Full Control (administrator) role to every model..

[v-kkf-msft]

Hi @tuulia ,

Could you use the "Analyze in Excel" function for the dataset directly on Service?

Or if you have the connection file for this dataset, you can open it in the data source settings.

     

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tuulia]

Hi! This is weird, I can't use "Analyze in Excel" function anymore. This function opens an Excel workbook, but there are no tabular mobel to browse. I am an owner of all datasets and I have "full control" role in every tabular model.

When I open Excel and make connection to model (get data -> from database -> from analysis services) everything works fine

[v-kkf-msft]

Hi @tuulia ,

Do you have the following options enabled in the Tenant settings?

If the option "Allow live connection" is disabled, you can not see "Analyze in Excel" in Service, like this:

If the option "Allow XMLA endpoints and Analyze in Excel with on-premises datasets" is disabled, when you use analyze in excel for datasets with live connect mode, you will get the error:

And this prevents you from connecting in excel to the dataset built with a live connection tabular model.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[tuulia]

Hi! Thanks, here my current situation after updating Excel etc.

- "Analyze in Excel" works fine with datasets

- I can now find my tabular model-datasets via Excel (Data -> get data -> from PowerBI)

- I can create Excel-reports from these datasets and publish Excel-reports to workspaces in PowerBI.com. (These reports uses tabular model datasets: Data -> get data -> from PowerBI)

But I can't refresh data or use Excel slicers. I get this Error: "External data refresh failed. We were unable to refresh one or more data connections in this workbook.
The following connections failed to refresh: "dataset_name"

This dataset works fine with PowerBI-reports