PowerQuery Relationship Troubleshooting

Rosie_222

New Member
Joined
Apr 19, 2020
Messages
4
Office Version
365
Platform
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

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Rosie_222

New Member
Joined
Apr 19, 2020
Messages
4
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 19, 2020
Messages
4
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 19, 2020
Messages
4
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,902
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top