Forum Discussion

Flipper's avatar
Flipper
Frequent Visitor
4 years ago

Using Excel Connector with PBI Datamodel

Hi Guys   I'm working with a client who basically wants to create a set of data that can be consumed through Excel, so I've recommened that they can use PBI to build out a datamodel and then use th...
Need Help
Tips and Tricks
Flipper's avatar
Flipper
Frequent Visitor
4 years ago

Hi Xiaoxin

 

Thanks for responding, but unfortunately I don't think the issue I see is to do with the relationships in excel 😞 That link talks about maintaining the relationships in Excel - this issue is to do with excel connecting to a PBI model on the PBI service.

 

The above scenario I gave was completely modelled in PBI - the data was created in PBI using the "Enter Data" option and the relationship between the tables is modelled in PBI. This is the model - its about as simple as it can be:

 

 

This is then the output (In PBI) if I put everything from the two tables into a table viz:

 

If I then publish to the PBI service and then connect to the same model using the Excel PBI connector, I see the following:

 

 

It doesn't make sense to me - excel is just the front end here, the relationship is correctly modelled in PBI and is proved to work in PBI, but fails when displayed using excel. Its as if the PowerPivot can't recognise the PBI model?

 

You can try it yourself if you want (I'm sorry, not sure how you attach an example!!) - all you need to do is lift the tables from the above and build the model in PBI - then publish it and connect using excel.

 

Thanks

 

Nick 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Flipper,

According to your description, this scenario seems like the relationship does not work correctly when you get data to excel. (as snapshot displayed, these two table records has been cross joined without relationship mapping and analysis)

Have you tested with other datasets to confirm if the relationship can be recognized on your excel data model?
If both of them do not work, I'd like to suggest you contact to power bi team with some detailed information (e.g.excel version, power bi desktop version, excel connector, and data driver) to get further support for troubleshooting.
Regards,

Xiaoxin Sheng