Transform Table with Power Query

[JGordon11]

How would one transform the green table on the left into the blue table on the right using power query?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Name	Year	Purchases	Vehicles		ID	Name	Year	Cars	Motorcycles	Total
2	123	A	2020	13065	Cars		123	A	2020	13065	35997	49062
3	456	B	2020	54220	Cars		456	B	2020	54220		54220
4	789	C	2020	25227	Cars		789	C	2020	25227	62602	87829
5	246	D	2020	73224	Cars		246	D	2020	73224		73224
6	357	E	2020	73505	Cars		357	E	2020	73505	105201	178706
7	468	F	2020	110148	Cars		468	F	2020	110148	24807	134955
8	123	A	2020	35997	Motorcycles		999	G	2020		16750	16750
9	999	G	2020	16750	Motorcycles		888	H	2020		69792	69792
10	789	C	2020	62602	Motorcycles		Subtotal		2020	349389	315149	664538
11	888	H	2020	69792	Motorcycles		123	A	2021	52150		52150
12	357	E	2020	105201	Motorcycles		999	G	2021		23500	23500
13	468	F	2020	24807	Motorcycles		Subtotal		2021	52150	23500	75650
14	123	A	2021	52150	Cars		Total			401539	338649	740188
15	999	G	2021	23500	Motorcycles
Append1

 

[sandy666]

Thanks for the documentation reference.

You are welcome
Have a nice day

 

[Bo_Ry]

Works for more Vehicles

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ID", "Name", "Year", "Vehicles"}, {{"Total", each List.Sum([Purchases]), type number}}),
    Pivoted = Table.Pivot(Group, List.Distinct(Group[Vehicles]), "Vehicles", "Total", List.Sum),
    GroupedYear = Table.Group(Pivoted, {"Year"}, {{"A", each Table.FromRows(Table.ToRows(_)&{{"Subtotal",null,List.Min([Year])}&List.Accumulate(List.Range(Table.ColumnNames(_),3),{},(s,l)=> s&{List.Sum(Table.ToColumns(Table.SelectColumns(_,l)){0})}  ) },Table.ColumnNames(_) )  }}),
    Subtotal = Table.Combine(GroupedYear[A]),
    TotalRow = Table.FromRows(Table.ToRows(Subtotal)&{{"Total",null,null}& List.Accumulate(List.Range(Table.ColumnNames(Pivoted),3),{},(s,l)=> s&{List.Sum(Table.ToColumns(Table.SelectColumns(Pivoted,l)){0})}  )},Table.ColumnNames(Pivoted) ),
    TotalColumn = Table.AddColumn(TotalRow, "Total", each List.Sum(List.Range(Record.ToList(_),3)) )
in
    TotalColumn

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Name	Year	Purchases	Vehicles		ID	Name	Year	Cars	Motorcycles	Bike	Total
2	123	A	2020	13065	Cars		123	A	2020	13065	35997		49062
3	456	B	2020	54220	Cars		246	D	2020	73224			73224
4	789	C	2020	25227	Cars		357	E	2020	73505	105201		178706
5	246	D	2020	73224	Cars		456	B	2020	54220			54220
6	357	E	2020	73505	Cars		468	F	2020	110148	24807		134955
7	468	F	2020	110148	Cars		789	C	2020	25227	62602		87829
8	123	A	2020	35997	Motorcycles		888	H	2020		69792		69792
9	999	G	2020	16750	Motorcycles		999	G	2020		16750		16750
10	789	C	2020	62602	Motorcycles		Subtotal		2020	349389	315149		664538
11	888	H	2020	69792	Motorcycles		123	A	2021	52150			52150
12	357	E	2020	105201	Motorcycles		999	G	2021		23500		23500
13	468	F	2020	24807	Motorcycles		Subtotal		2021	52150	23500		75650
14	123	A	2021	52150	Cars		888	Z	2022			234	234
15	999	G	2021	23500	Motorcycles		Subtotal		2022			234	234
16	888	Z	2022	234	Bike		Total			401539	338649	234	740422
Sheet1

 

[sandy666]

I'd add this line and will be fine

Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))