Is merging queries smart?

[WilliamD89]

I have two datasets that share similar employee data (names etc.), I am merging these datasets on an Employee ID. 

Some Employee IDs are blank in both datasets, but left joining the datasets seems to bring back the correct records for those employee data anyway (in something like SQL I would only expect matching Employee IDs to be merged).

Is Power BI doing some smart matching other than a straight join on the Employee ID in both datasets without stating this?

[Anonymous · Accepted solution]

@WilliamD89 

There are different join kinds for mergeing

Power BI Merge Queries and Append Queries (mssqltips.com)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[smpa01]

@WilliamD89one aspect to be aware of with employee IDs blank on left table with blank employee IDs on right table, the left join on left table would work as crossjoin. In that case, the merged table will have duplicate rows. If you do an innerjoin instaed of left join, this problem can be avoided.

let 
   right= let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSjRUitWBsIyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, addreess = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"addreess", type text}})
in
    #"Changed Type" ,

left= let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMlSK1QEzjJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"val", Int64.Type}})
in
    #"Changed Type",
    #"Merged Queries" = Table.NestedJoin(left, {"id"}, right, {"Column1"}, "left", JoinKind.LeftOuter),
    #"Expanded left" = Table.ExpandTableColumn(#"Merged Queries", "left", {"Column1", "addreess"}, {"Column1", "addreess"})

    in #"Expanded left"

[WilliamD89]

Thank you for the reply, that is how a typical SQL join would work also. So I am probably seeing the correct match but if I looked further would see the incorrect match (for the other blanks).

[smpa01]

@WilliamD89I think SQL left join on null keys work differently than PQWRY