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

[LarsAustin]

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

[Vijay_A_Verma · Accepted solution]

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]

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]

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"

[LarsAustin]

Hi Vijay,

Thank you for the feedback. But it didn't provide the solution I wanted. Below is the end output based on the M code you have provided (I added an Index column on the Actual table as you indicated):

It is missing component G for both works order that is in the Standard table but not on Actual table.

Thank you

Jojemar

[Vijay_A_Verma]

Use this and also there is no need for Index column now

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"}),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"Product", "Component"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates1",{"Product", "Component", "Actual", "Standard", "Work Order"})
in
    #"Reordered Columns"

[LarsAustin]

Hi Vijay,

Thanks again for exploring the solution for my problem. It is showing now the Component from the Standard that is missing from the Actuals, but it only shows the result for Work Oder AA1 and not in AA2. Below is the end output based on your amended M code:

Thank you

Jojemar

[LarsAustin]

Hi Vijay,

Amazing. Works perfectly. 

Thanks so much..

Jojemar