Merge two tables with subtraction
Sources Result Code Cost Code Cost FMV Adjustment 10203006 3528377.9210203006 3528377.92 3528377.91 0.01 30703001 1666190.3430703001 1666190.34 1273727 392463.34Ycode FMV 10203006 392041.99 10203006 130680.66 10203006 392041.99 10203006 2613613.27 30703001 1157933.64 30703001 115793.36
Code: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




Bookmarks