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"
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,
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
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"