PQ - Consolidate two tables

[sandy666]

Consolidate two tables


Table1Table2Result

YEARVESSELNRNET CLAIMYEARUNDERWRITERSHAREVESSELNRPREMIUMYEARUNDERW RITERVESSELPremiumClaim


2017A
1
0
2017X0,6A
1
2500
2017XA
2500
0


2017B
2
2000
2017X0,6B
2
2750
2017YA
2850
0


2017B
2
3000
2017X0,6C
3
1750
2017XB
5500
5000


2017C
3
0
2018X0,6A
1
2900
2017YB
6200
5000


2018A
1
1750
2018X0,6B
2
3150
2017XC
1750
0


2018B
2
3000
2018X0,6C
3
2150
2017YC
2100
0


2018B
2
4000
2019X0,6A
1
3500
2018XA
2900
1750


2018B
2
5000
2019X0,6B
2
3400
2018YA
3250
1750


2018C
3
0
2019X0,6C
3
4000
2018XB
9450
12000


2019A
1
0
2017Y0,4A
1
2850
2018YB
10500
12000


2019B
2
5000
2017Y0,4B
2
3100
2018XC
2150
0


2019C
3
20000
2017Y0,4C
3
2100
2018YC
2500
0


2018Y0,4A
1
3250
2019XA
3500
0


2018Y0,4B
2
3500
2019YA
3850
0


2018Y0,4C
3
2500
2019XB
3400
5000


2019Y0,4A
1
3850
2019YB
3750
5000


2019Y0,4B
2
3750
2019XC
4000
20000


2019Y0,4C
3
4350
2019YC
4350
20000



let
Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Merge = Table.NestedJoin(Tbl1,{"YEAR", "VESSEL", "NR"},Tbl2,{"YEAR", "VESSEL", "NR"},"Tbl2",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Tbl2", {"UNDERWRITER", "SHARE", "PREMIUM"}, {"UNDERWRITER", "SHARE", "PREMIUM"}),
TSC = Table.SelectColumns(Expand,{"YEAR", "UNDERWRITER", "VESSEL", "PREMIUM", "NET CLAIM"}),
Group = Table.Group(TSC, {"YEAR", "UNDERWRITER", "VESSEL"}, {{"Premium", each List.Sum([PREMIUM]), type number}, {"Claim", each List.Sum([NET CLAIM]), type number}}),
Sort = Table.Sort(Group,{{"YEAR", Order.Ascending}, {"VESSEL", Order.Ascending}, {"UNDERWRITER", Order.Ascending}})
in
Sort