Using Excel Connector with PBI Datamodel

[Flipper]

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 the excel connector to then consume it in Excel.

I've built the model for them, for the sake of argument, assume its two connected tables with a One to Many relationship.

If I build a report from this in PBI, it works as I would expect - i.e. I can bring together data from the two tables and the data is correctly related to one another.

However, when I use Excel and connect to datamodel I have published, if I try to pull the data from both tables, it acts as of there is  a full outer join going on - i.e. every record for the main table appears against every record of the other table.

I've checked this against other datamodels that I have built previously and interestingly I see the same behaviour - which makes me think this is just the way the excel connector works (I don't have much experience with the connector though). However, it feels wrong. Does anyone have a bit of experience using the excel connector and tell me if there are any tricks to using it in a better way?

Incidently, I know I can work round this problem by builing bespoke measures to effectively force the relationships, however the client would like to have a measure of control over what is displayed, so ideally I'd be able to make the PowerPivot in Excel work in a similar way to something like the matrix visualisation in PBI.

Thanks

Nick

[amitchandak]

@Flipper ,Based on what I got.

In case table on the one side have less values say have 5 category and fact(many side table) has 6.

You will get one blank row with all measures created from fact when they are used with category.

By default is it right join. +0 can force left also.

In case you want to remove the blank of one side when value is missing ;you need to use a visual or page level filter for that

[Flipper]

Hi @amitchandak 

Thanks for replying. So probably helps to have an example. I've got two tables, lets call them Parent and Child.

Parent has:

ParentID

Parent1

Parent2

Child has

ChildID	ParentIDfk
Child1	Parent1
Child2	Parent1
Child3	Parent2

If I bring them together, I'd expect to see:

ParentID	ChildID	ParentIDfk
Parent1	Child1	Parent1
Parent1	Child2	Parent1
Parent2	Child3	Parent2

However, what I believe I am seeing is (In excel - NOT in PBI, works as above in PBI):

ParentID	ChildID	ParentIDfk
Parent1	Child1	Parent1
Parent1	Child2	Parent1
Parent1	Child3	Parent2
Parent2	Child1	Parent1
Parent2	Child2	Parent1
Parent2	Child3	Parent2

The above feels wrong, but here is the evidence....

Weird eh?

[Anonymous]

Hi @Flipper,

It seems like these records do not map correctly in the pivot table, have you built the relationship in excel data tables? If not, you can refer to the following link to build the relationship to help records mapping.

Create a relationship between tables in Excel (microsoft.com)

Regards,

Xiaoxin Sheng

[Flipper]

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]

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