Merging tables with Left Join returns rows with no match

[mbixby]

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. 

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

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

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:

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

ID	Reservation_ID	Value
1	AAA	red
2	BBB	orange
3	CCC	yellow
4	DDD	green
5	EEE	blue
6	FFF	indigo
7	GGG	violet
8	HHH	pink
9	III	black
10	JJJ	white

B1

id	Reservation_ID	SubCaseID	Value
1	AAA	A1	red
2	BBB	B1	orange
3	CCC	C1	yellow
4	DDD	D1	green
5	EEE	E1	blue
6	FFF		indigo
7	HHH		violet
8			pink
9			black
10			white

C1

id	CaseID	SubCase	Value
1	A1	AA1	red
2	B1	BB1	orange
3	C1	CC1	yellow
4	D1	DD1	green
5	E1	EE1	blue
6	F1		indigo
7	G1		violet
8	H1		pink
9			black
10			white

[Mariusz · Accepted solution]

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.

[mbixby]

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.  

[Jimmy801]

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