How to join two queries preventing double values

[BeeJay]

I will try to describe it easily.

I'm trying to combine 2 queries into one so I can create a pivot table out of it.

I have made one query from my first Table1 (see below) with Claimfigures for vessels and one query from my second table below with Premiumfigures per vessel.

My problem is that per year each vessel can only have one premium listed per Underwiter, however each vessel can have per year multiple claims. If I combine the two queries I get for each vessel for each claim also the premium listed. So a vessel that has two claims, will also show two times the premiums, which should not be the case. I can't figure out how to solve this.

Would much appreciate if somebody can put me on the right track.

 

[alansidman]

Please use the XL2BB function to upload these samples so that we don't have to retype your data. Cannot manipulate data in pictures.

 

[BeeJay]

Ah, I'm sorry about that.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table1	DataClaims					Table2	Data premiums
2
3	YEAR	VESSEL	NR	NET CLAIM			YEAR	UNDERWRITER	SHARE	VESSEL	NR	PREMIUM
4	2017	A	1	-			2017	X	60%	A	1	2.500
5	2017	B	2	2.000			2017	X	60%	B	2	2.750
6	2017	B	2	3.000			2017	X	60%	C	3	1.750
7	2017	C	3	-			2018	X	60%	A	1	2.900
8	2018	A	1	1.750			2018	X	60%	B	2	3.150
9	2018	B	2	3.000			2018	X	60%	C	3	2.150
10	2018	B	2	4.000			2019	X	60%	A	1	3.500
11	2018	B	2	5.000			2019	X	60%	B	2	3.400
12	2018	C	3	-			2019	X	60%	C	3	4.000
13	2019	A	1	-			2017	Y	40%	A	1	2.850
14	2019	B	2	5.000			2017	Y	40%	B	2	3.100
15	2019	C	3	20.000			2017	Y	40%	C	3	2.100
16							2018	Y	40%	A	1	3.250
17							2018	Y	40%	B	2	3.500
18							2018	Y	40%	C	3	2.500
19							2019	Y	40%	A	1	3.850
20							2019	Y	40%	B	2	3.750
21							2019	Y	40%	C	3	4.350
Blad1

 

[alansidman]

If this looks like what you are trying to achieve, then the explanation follows.

Book1
	A	B	C	D	E
1	YEAR	UNDERWRITER	VESSEL	PREMIUM	Total Claims
2	2017	X	A	2500	6051
3	2018	X	A	2900	6054
4	2019	X	A	3500	6057
5	2017	Y	A	2850	6051
6	2018	Y	A	3250	6054
7	2017	X	B	2750	12102
8	2018	X	B	3150	12108
9	2019	X	B	3400	12114
10	2017	Y	B	3100	12102
11	2017	X	C	1750	6051
12	2018	X	C	2150	6054
13	2019	X	C	4000	6057
14	2017	Y	C	2100	6051
15	2018	Y	B	3500	12108
16	2018	Y	C	2500	6054
17	2019	Y	A	3850	6057
18	2019	Y	B	3750	12114
19	2019	Y	C	4350	6057
Sheet2

Brought both tables into Power Query. Merged the two tables. Then accumulated the claims. Advise if this presentation works for you and if so, then will supply more details.

 

[BeeJay]

Dear Alan,

Thanks for your efforts, it much looks like what I'm trying to get, however the totals in the column Total Claims are incorrect. Below the correct output

YEAR	UNDERWRITER	VESSEL	PREMIUM	Total Claims
2017	X	A	2.500	-
2017	Y	A	2.850	-
2017	X	B	2.750	5.000
2017	Y	B	3.100	5.000
2017	X	C	1.750	-
2017	Y		2.100	-
2018	X	A	2.900	1.750
2018	Y	A	3.250	1.750
2018	X	B	3.150	12.000
2018	Y	B	3.500	12.000
2018	X	C	2.150	-
2018	Y	C	2.500	-
2019	X	A	3.500	-
2019	Y	A	3.850	-
2019	X	B	3.400	5.000
2019	Y	B	3.750	5.000
2019	X	C	4.000	20.000
2019	Y	C	4.350	20.000

 

[alansidman]

Book1
	A	B	C	D	E
1	YEAR	UNDERWRITER	VESSEL	PREMIUM	Total Claims
2	2017	X	A	$2,500.00	$-
3	2017	Y	A	$2,850.00	$-
4	2017	X	B	$2,750.00	$5,000.00
5	2017	Y	B	$3,100.00	$5,000.00
6	2017	X	C	$1,750.00	$-
7	2017	Y	C	$2,100.00	$-
8	2018	X	A	$2,900.00	$1,750.00
9	2018	Y	A	$3,250.00	$1,750.00
10	2018	X	B	$3,150.00	$12,000.00
11	2018	X	C	$2,150.00	$-
12	2019	X	A	$3,500.00	$-
13	2019	X	B	$3,400.00	$5,000.00
14	2019	X	C	$4,000.00	$20,000.00
15	2018	Y	B	$3,500.00	$12,000.00
16	2018	Y	C	$2,500.00	$-
17	2019	Y	A	$3,850.00	$-
18	2019	Y	B	$3,750.00	$5,000.00
19	2019	Y	C	$4,350.00	$20,000.00
Sheet2

Here is the Mcode for the merged tables

let
    Source = Table.NestedJoin(Table2, {"YEAR", "VESSEL"}, Table1, {"YEAR", "VESSEL"}, "Table1", JoinKind.FullOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"NET CLAIM"}, {"Table1.NET CLAIM"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table1", {"YEAR", "UNDERWRITER", "VESSEL", "PREMIUM"}, {{"Total Claims", each List.Sum([Table1.NET CLAIM]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"PREMIUM", Currency.Type}, {"Total Claims", Currency.Type}})
in
    #"Changed Type"

 

[BeeJay]

That looks promissing. Will try to implement it for my query. Thanks.

 

[sandy666]

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