Forum Discussion
Merge Two Tables Based on Two-Columns To Keep All Unique Records/Items From Both Table
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"
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"
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
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"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