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 output 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
Hi Jojemar,
First of all you need to do a full outer join. Then add a custom column that fills the null components in the first table with the component from the second table. Then remover the two original component columns.
The attached file demonstrates what I mean.
Hi Jojemar
The quick way is to Append table 2 to table 1, then Group by Product and Component, two aggregations Actual and Standards Just Min or Max it
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Component", type text}, {"Actual", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", Table2}),
#"Grouped Rows" = Table.Group(#"Appended Query", {"Product", "Component"}, {{"Actual", each List.Min([Actual]), type number}, {"Standard", each List.Min([Standard]), type number}})
in
#"Grouped Rows"
See Attached
