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]

it's possible with Power Query but why make your life difficult if you can use Pivot Table

 

[JGordon11]

I'm trying to learn Power Query and this example seems like it would cover a lot of transformation types and useful functions that I could refer to.

 

[sandy666]

sure
IMHO you should choose better example and not imitate PT
anyway here is link for learning

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[JGordon11]

Thanks

 

[sandy666]

Welcome

 

[Bo_Ry]

Please try

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.Sum([Cars]),List.Sum([Motorcycles]) }},Table.ColumnNames(_) )  }}),
    Subtotal = Table.Combine(GroupedYear[A]),
    TotalRow = Table.InsertRows(Subtotal,Table.RowCount(Subtotal),{[ID="Total",Name=null,Year=null,Cars=List.Sum(Pivoted[Cars]),Motorcycles=List.Sum(Pivoted[Motorcycles])]}),
    TotalColumn = Table.AddColumn(TotalRow, "Total", each List.Sum({[Cars],[Motorcycles]}))
in
    TotalColumn

 

[JGordon11]

Please try

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.Sum([Cars]),List.Sum([Motorcycles]) }},Table.ColumnNames(_) )  }}),
    Subtotal = Table.Combine(GroupedYear[A]),
    TotalRow = Table.InsertRows(Subtotal,Table.RowCount(Subtotal),{[ID="Total",Name=null,Year=null,Cars=List.Sum(Pivoted[Cars]),Motorcycles=List.Sum(Pivoted[Motorcycles])]}),
    TotalColumn = Table.AddColumn(TotalRow, "Total", each List.Sum({[Cars],[Motorcycles]}))
in
    TotalColumn

Replicates perfectly. Very cool that all that can be done in just seven steps. Thanks for the lesson!

 

[sandy666]

it doesn't work if source will be updated, even if it will be one row

ID	Name	Year	Purchases	Vehicles		ID	Name	Year	Cars	Motorcycles	Bike	Total
123	A	2020	13065	Cars		123	A	2020	13065	35997		49062
456	B	2020	54220	Cars		246	D	2020	73224			73224
789	C	2020	25227	Cars		357	E	2020	73505	105201		178706
246	D	2020	73224	Cars		456	B	2020	54220			54220
357	E	2020	73505	Cars		468	F	2020	110148	24807		134955
468	F	2020	110148	Cars		789	C	2020	25227	62602		87829
123	A	2020	35997	Motorcycles		888	H	2020		69792		69792
999	G	2020	16750	Motorcycles		999	G	2020		16750		16750
789	C	2020	62602	Motorcycles
888	H	2020	69792	Motorcycles		123	A	2021	52150			52150
357	E	2020	105201	Motorcycles		999	G	2021		23500		23500
468	F	2020	24807	Motorcycles
123	A	2021	52150	Cars		888	Z	2022			234
999	G	2021	23500	Motorcycles
888	Z	2022	234	Bike

 

[JGordon11]

I agree that PT is the way to go if this were a real data table that would change over time. But I was just trying to see some PQ steps and dive into the function references to understand how they work on a particular example. I'll keep poking around on this forum to see if there are some other examples to learn from. Thanks for the documentation reference.