Merge two tables with subtraction
Sources |
|
|
Result |
|
|
|
|
|
|
|
|
|
|
Code |
Cost |
|
Code |
Cost |
FMV |
Adjustment |
10203006 |
3528377.92 |
|
10203006 |
3528377.92 |
3528377.91 |
0.01 |
30703001 |
1666190.34 |
|
30703001 |
1666190.34 |
1273727 |
392463.34 |
|
|
|
|
|
|
|
Ycode |
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