Forum Discussion

LarsAustin's avatar
LarsAustin
Helper I
4 years ago
Solved

Merge Two Tables Based on Two-Columns To Keep All Unique Records/Items From Both Table

Hi,   I was trying to merge an Actuals table to a Standard table based on columns Product and Component using a Left Outer join. Below is the M code from the Advanced Editor: let Source = Table.N...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this code

    let
    ProductsList = List.Distinct(Actual[Product]),
    #"Merged Queries" = Table.NestedJoin(Actual, {"Product", "Component"}, Standard, {"Product", "Component"}, "Standard.1", JoinKind.LeftOuter),
    #"Expanded Standard.1" = Table.ExpandTableColumn(#"Merged Queries", "Standard.1", {"Standard"}, {"Standard"}),
    #"Appended Query" = Table.Combine({#"Expanded Standard.1", Standard}),
    #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each List.Contains(ProductsList,[Product])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Product", "Component"})
in
    #"Removed Duplicates"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Use this code

let
    ProductsList = List.Distinct(Actual[Product]),
    #"Merged Queries" = Table.NestedJoin(Actual, {"Product", "Component"}, Standard, {"Product", "Component"}, "Standard.1", JoinKind.LeftOuter),
    #"Expanded Standard.1" = Table.ExpandTableColumn(#"Merged Queries", "Standard.1", {"Standard"}, {"Standard"}),
    #"Appended Query" = Table.Combine({#"Expanded Standard.1", Standard}),
    #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each List.Contains(ProductsList,[Product])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Product", "Component"})
in
    #"Removed Duplicates"
LarsAustin's avatar
LarsAustin
Helper I
4 years ago

Hi Vijay,

 

Amazing. Works perfectly. 

 

Thanks so much..

 

Jojemar