PQ - Merge two tables with subtraction

[sandy666]

Merge two tables with subtraction


SourcesResult



CodeCostCodeCostFMVAdjustment


10203006
3528377.9210203006
3528377.92
3528377.91
0.01


30703001
1666190.3430703001
1666190.34
1273727
392463.34



YcodeFMV


10203006
392041.99


10203006
130680.66


10203006
392041.99


10203006
2613613.27


30703001
1157933.64


30703001
115793.36



let
Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Merge = Table.NestedJoin(Tbl1,{"Code"},Tbl2,{"Ycode"},"Tbl2",JoinKind.LeftOuter),
FMVList = Table.AddColumn(Merge, "FMV", each [Tbl2][FMV]),
Expand = Table.ExpandListColumn(FMVList, "FMV"),
TSC = Table.SelectColumns(Expand,{"Code", "Cost", "FMV"}),
Group = Table.Group(TSC, {"Code", "Cost"}, {{"FMV", each List.Sum([FMV]), type number}}),
Adj = Table.AddColumn(Group, "Adjustment", each [Cost] - [FMV], type number),
Type = Table.TransformColumnTypes(Adj,{{"Code", type text}, {"Cost", type number}})
in
Type