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.NestedJoin(Actual, {"Product", "Component"}, Standard, {"Product", "Component"}, "Standard", JoinKind.LeftOuter),
#"Expanded Standard" = Table.ExpandTableColumn(Source, "Standard", {"Standard"}, {"Standard"})
in
#"Expanded Standard"

 

 

 

This is the ouput that I get as expected:

 

 

But my desired output is the table below:

 

 

I want to be able to capture one of the items in the standard that is missing from the actual.

 

I tried using different merge types but it is not giving me the exact result i wanted.

 

I will appreciate your inputs to make this work.

 

Thank you

 

Jojemar

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"

7 Replies

  • 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 

    • LarsAustin's avatar
      LarsAustin
      Helper I
      4 years ago

      Hi Vijay,

       

      Slight change in the scenario. I added Work Oder column to my original Actual table as below:

       

       

      The Standard table stays the same.

       

      Now, my desired output table will be something like this:

       

      What needs to be change in the M code?

       

      Thanks so much.

       

      Jojemar

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Insert an Index column in Actual table. Index column is required to preserve sort order in result.

        Use following 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", "Work Order"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each ([Index] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Product", "Component", "Actual", "Standard", "Work Order"})
in
    #"Reordered Columns"