Forum Discussion
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 t...
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.
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"
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).