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 three data tables from three separate sources that should reconcile to each other, in theory, but do not. I would like to create a "Master" table from the three in order to build an exception list and identify the non-reconclied items. The unique identifier is the same across all three tables and duplicate unique IDs within any table can exist.
Something like combine Table1[UniqueID], Table2[UniqueID] & Table3[UniqueID] into MasterTable[UniqueID] so that I have a single column table of all unique IDs with all duplicates removed. From there, I can build my cross references and exception report.
Any help is greatly appreciated.
Solved! Go to Solution.
Hi @tango1201,
I try to reproduce your scenario, I create the following sample tables.
Append the two tables together by clicking Test1->Append Query->Append Test2 table.
Remove other columns, only leave UniqueID column, them remove the duplicates. You will get the following result table.
Here is my Query statement.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VIrViVYyMjIC08bGxmDaxMREKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UniqueID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}}), #"Appended Query" = Table.Combine({#"Changed Type", Test2}), #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"name"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"
Best Regards,
Angelia
Hi @tango1201,
I try to reproduce your scenario, I create the following sample tables.
Append the two tables together by clicking Test1->Append Query->Append Test2 table.
Remove other columns, only leave UniqueID column, them remove the duplicates. You will get the following result table.
Here is my Query statement.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VIrViVYyMjIC08bGxmDaxMREKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UniqueID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}}), #"Appended Query" = Table.Combine({#"Changed Type", Test2}), #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"name"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"
Best Regards,
Angelia
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.
User | Count |
---|---|
105 | |
97 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |