PowerQuery Relationship Troubleshooting

[Rosie_222]

Hey All,

Apologies if a similar query has already been posted and answered; I did my best to search before posting but it's one of those problems that was hard to articulate precisely and succinctly in a search. I'm relatively new to powerquery and powerpivot having built 3-5 prototypes successfully. This is the first time I've encountered this problem.

General Details:
Using Microsoft Office 365 proplus on a windows machine. Annoyingly having to wing it on x32bit since IT are currently refusing to upgrade me to 64. (I do have access to another machine with 64 though)

The data I have is from a task management tool where essentially there are parent 'issues' and child 'issues'. Child issues have their own issue ID but I can also get their parent ID too. This obviously lends itself very nicely to being used to create a relationship and as such I have an export of just the child issues (several of which can share the same parent so Parent ID is the foreign Key) and another export of all the parent issues (issue ID here is the primary key). I was able to successfully create the relationship between the two as the data was arranged such that there's a 1 to many relationship, as required. This should mean though that when I look at both Issue ID from the parent issues export and Parent ID from the child issues export together in the row labels in the pivot table they should both report the same ID and no others. However instead it seems despite the relationship being created successfully, the Issue ID from the parent data set is matching to all Parent IDs from the child data set. I've tried to attach some screenshots that help illustrate the situation.

PLEASE NOTE: this isn't the classic problem where you drag in a field into the values section that you can't report on based on the relationships that are active and you get the same values repeated. Both fields are in the row labels.

I'm really perplexed and would really appreciate any help anyone can provide Happy to provide further info as required

 

[sandy666]

this is NOT Power Query as in title but Power Pivot

 

[Rosie_222]

Hey,

I've still not managed to resolve this myself and would love to understand why it is happening. This particular data set isn't massive so I can workaround it by not using power pivot o) and instead using a look up table but I would love to understand why this is happening to help my general understanding and learning. Also longer term I do think this particular project would be better managed via powerpivot.

Any help would be much appreciated! Thanks in advance

 

[Rosie_222]

It is also worth bearing in mind that a simple 'clean' and 'trim' on the primary and foreign keys doesn't fix this issue either. It seems to me that the relationship can be created since there are not duplicate values but it isn't matching the values effectively and therefore must see them as different somehow. I've verified they are the same data type and as mentioned have tried a trim and clean and neither has worked. Been doing a fair bit of googling but still haven't located any solutions.

 

[Rosie_222]

It is also worth bearing in mind that a simple 'clean' and 'trim' on the primary and foreign keys doesn't fix this issue either. It seems to me that the relationship can be created since there are not duplicate values but it isn't matching the values effectively and therefore must see them as different somehow. I've verified they are the same data type and as mentioned have tried a trim and clean and neither has worked. Been doing a fair bit of googling but still haven't located any solutions.

I also forgot to say that when considering the individual CSVs the data appears to match i.e when I enter
=CSV1!B1=CSV2!B1
I get TRUE which indicates the 2 cells are genuinely equal and should match. Additionally I can match them up using a MATCH formula with no trimming etc. It makes me wonder whether the format of the data set when the query is created is a problem? So not the columns within the queries but the query itself, i.e whether it's 65001: Unicode (UTF-8) or 1252 Western European (Windows) etc? I know that using the former can correct some of the weird characters you get in CSVs when you download data but tbh I don't really know much about or understand the true differences.

 

[pjmorris]

Hi Rosie,

I get exactly the same behaviour as you until I drag something (anything) from the Child table into the values area - then it works ok. I don't know why it behaves like this, but I've seen it before.

Not sure it helps much other than to confirm that the behaviour is what you'd expect in this situation as far as I know.

HTH