Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tango1201
Frequent Visitor

Master Table for Cross Referencing

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.

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @tango1201,

I try to reproduce your scenario, I create the following sample tables.

1.PNG2.PNG

Append the two tables together by clicking Test1->Append Query->Append Test2 table.

3.png

Remove other columns, only leave UniqueID column, them remove the duplicates. You will get the following result table.

6.PNG

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

View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @tango1201,

I try to reproduce your scenario, I create the following sample tables.

1.PNG2.PNG

Append the two tables together by clicking Test1->Append Query->Append Test2 table.

3.png

Remove other columns, only leave UniqueID column, them remove the duplicates. You will get the following result table.

6.PNG

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.