Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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).
@WilliamD89I think SQL left join on null keys work differently than PQWRY
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.