Forum Discussion

tango1201's avatar
tango1201
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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