Analyze in Excel - Data Model Missing in Excel

[Anonymous]

Hi all - 

I am trying to use "Analyze in Excel" to bring my Power BI data model out into Microsoft Excel to build reports for my clients ( who prefer to work in Excel). I have successfully saved the excel file with the pbiazure connection from the Power BI service and built a pivot table. However, when i try to click "Manage Data model" in excel to see the relationships (that i expect would be the same as Power BI desktop), it doesn't show any data model and is just blank. 

Does anyone have any idea why this is the case? In case it is directly connected to the dataset in the Power BI service, my assumption would be that it would be able to show and edit the data model directly in Excel. 

Can anyone shed light on why it is just showing a blank screen with no data model ?

[v-yingjl]

Hi @Anonymous ,

'Manage data model' option in excel will only show the data model when you connect data source in excel. A Data Model is created automatically when you import two or more tables simultaneously from a database. When you import one table, you can select 'add this data to data model'. Refer this article: Advanced Excel - Data Model 

In this issue, 'Analyze in excel' just quotes the dataset from power bi service, not as a single data source, this dataset is come from your .pbix file which has included relationships etc. so it will not be used as a single data source to be added to data model in excel.

In other words, the dataset itself has been a model, you can manage it in your power bi desktop not in excel to recreate a model.

Best Regards,
Yingjie Li

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

[Anonymous]

Hi @v-yingjl - 

Thanks for the clarification, that helps to understand a bit. So please clarify the following then: 

1) If the data model is not imported, will all the relationships between the data i have setup in the desktop file work when i pull use it as "Analyze in Excel"?

2) If i want to add any relationship or measure, i have to do that first in Power BI desktop, publish to the service, then it should show up in my excel file?

thanks!

[v-yingjl]

Hi @Anonymous ,

1. Yes, 'Analyze in excel' quotes the dataset in power bi service, relationships will retain.
2. If you want to add relationship or measure, you have to do that first in power bi desktop, then publish to service and re-use 'Analyze in excel', although you cannot see the relationship and concrete formula of the measure, you can only see the table fields and the value of measure in the excel file.

Best Regards,
Yingjie Li

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

[nandic]

Hi @Anonymous ,
Analyze in Excel allows you to interact with data set from Power BI, but it has only connection to data set, it doesn't have any of data from model.

Here is the link: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
"You can Save the Excel workbook you create with the Power BI dataset, just like any other workbook. However, you cannot publish or import the workbook back into Power BI, because you can only publish or import workbooks into Power BI that have data in tables, or that have a data model. Since the new workbook simply has a connection to the dataset in Power BI, publishing or importing it into Power BI would be going in circles".