Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
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
Hi Vijay,
Amazing. Works perfectly.
Thanks so much..
Jojemar
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.