PowerQuery Relationship Troubleshooting

Rosie_222

New Member
Joined
Apr 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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 :) PP_Relationship_Issue_Parent.PNG
 

Attachments

  • PP_Relationship.PNG
    PP_Relationship.PNG
    77.9 KB · Views: 25

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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 :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top