Forum Discussion

WilliamD89's avatar
WilliamD89
Frequent Visitor
5 years ago
Solved

Is merging queries smart?

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?

Need Help

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    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's avatar
      WilliamD89
      Frequent Visitor
      5 years ago

      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).