cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbixby
Frequent Visitor

Merging tables with Left Join returns rows with no match

I have several tables that I need to combine.  They should merge together as chain with each table containing a smaller set of related data.  Merging the queries with a left outer join should be all that I need but after attempting the merge I end up with odd extra records in the 'right' merged table.

 

I've recreated my issue with sample data (below). I have 3 tables to Merge: Reservations, B1, and C1.  Reservations has a relationship with B1 on field reservation_id, B1 has a further relationship with C1 on SubCaseID and CaseID respectively.  

 

When I try to Merge just B1 and C1, using B1.SubCaseId and C1.CaseId, I get the non-matching C1 rows repeated for each unmatched B1 row.  

 

In SQL the result of a left join to B1 and C1 on B1.subcase with C1.caseid (no explicit FKs), with the highlighted rows being C1 records that had a match to B1.  All unmatched rows from C1 are null.  This is what I need to reproduce. 

B1 Left Join C1.PNG

When I do (what I figured was the same operation in Power Query): 

MergeB1toC1.PNG

I get this (and I noticed that it is matching 10 of 10 rows - should be 5?):

MergeLeftResults.PNG

So can anyone help me understand why this is the result?  All the rows indicated in red should be NULL values.  I'm further puzzled because when I take Reservations and B1 and do what appears to be the same thing in power query (Merge Left Outer Join Reservations.Reservation_ID on B1.Reservation_ID) I get the desired behavior:

ReservationsMergeB1.PNG

 

The only thing different is there are 5 fields in B1 that match 5 fields in C1.  C1 has 3 fields that have no match to B1's and 2 fields that are null.  But I don't see how C1's 'extra' or null fields should affect the results?  And if they do - how do I get the desired dataset?

 

Thanks for the help in advance!  

 

Sample data used:

Reservations

IDReservation_IDValue
1AAAred
2BBBorange
3CCCyellow
4DDDgreen
5EEEblue
6FFFindigo
7GGGviolet
8HHHpink
9IIIblack
10JJJwhite

B1

idReservation_IDSubCaseIDValue
1AAAA1red
2BBBB1orange
3CCCC1yellow
4DDDD1green
5EEEE1blue
6FFF indigo
7HHH violet
8  pink
9  black
10  white

C1

idCaseIDSubCaseValue
1A1AA1red
2B1BB1orange
3C1CC1yellow
4D1DD1green
5E1EE1blue
6F1 indigo
7G1 violet
8H1 pink
9  black
10  white

 

1 ACCEPTED SOLUTION
Mariusz
Super User II
Super User II

Hi @mbixby 

 

My understanding is that Null is considered as value and it matches with Null value from another table where you have two Null, therefore you get two matching values per every Null row from the first table.

 

To get the desired result you can replace null values with "*" or something else in the first or second table, this will be read as mismatched records by the engine.

Please see the attached for reference

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

View solution in original post

3 REPLIES 3
Mariusz
Super User II
Super User II

Hi @mbixby 

 

My understanding is that Null is considered as value and it matches with Null value from another table where you have two Null, therefore you get two matching values per every Null row from the first table.

 

To get the desired result you can replace null values with "*" or something else in the first or second table, this will be read as mismatched records by the engine.

Please see the attached for reference

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

 

View solution in original post

mbixby
Frequent Visitor

Thank you!  If I replace the nulls in the left-joined table it does prevent the null fields from being in the joined set.  

This is unfortunate that PBI treats nulls this way - null should be, well null.  I can't see a benefit having nulls treated as values.  

Hello @mbixby ,

 

null is a value as any other. And if in some column that is joined is present a null and on the other side as well, why shouldn't these be treated? Simple do a filter of tables before you join them and you are alright. Or do you see any problems in doing so?


If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors