Forum Discussion
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...
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"
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