
New Member

July 28, 2022

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


Trusted Members
Moderators
Power BI

January 31, 2022



August 21, 2019

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
1 Guest(s)
