PQ - Number grouped rows

[sandy666]

Number grouped rows


source result

GroupCategory Sales GroupCategorySalesIndex

FoodBreakfast
99,562.64 AlcoholWine
217492.74
1

FoodSoups/Salads
113,642.33 AlcoholDraft Beer
132882.83
2

FoodSandwiches
217,510.36 AlcoholLiquor
50939.94
3

FoodEntrees
290,278.36 AlcoholCanned Beer
49996.55
4

FoodNon Alc Beverage
93,580.81 AlcoholBottled Beer
29043.99
5

FoodAppetizers
98,191.52 AlcoholCoolers/Ciders
22628.14
6

AlcoholDraft Beer
132,882.83 AlcoholLiquor Modifiers
9.3
7

FoodBurgers
138,000.37 FoodEntrees
290278.36
1

FoodDesserts
16,039.30 FoodSandwiches
217510.36
2

AlcoholWine
217,492.74 FoodBurgers
138000.37
3

AlcoholBottled Beer
29,043.99 FoodSoups/Salads
113642.33
4

AlcoholLiquor
50,939.94 FoodBreakfast
99562.64
5

AlcoholCoolers/Ciders
22,628.14 FoodAppetizers
98191.52
6

AlcoholCanned Beer
49,996.55 FoodNon Alc Beverage
93580.81
7

AlcoholLiquor Modifiers
9.30 FoodDesserts
16039.3
8

Food Cakes
234.55FoodCakes
234.55
9



let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Category", type text}, {"Sales", type number}}),
Sort = Table.Sort(Type,{{"Group", Order.Ascending}, {"Sales", Order.Descending}}),
Group = Table.Group(Sort, {"Group"}, {{"Data", each _, type table}}),
InnerIndex = Table.AddColumn(Group, "Partitioned", each Table.AddIndexColumn([Data], "Index", 1, 1)),
RC = Table.RemoveColumns(InnerIndex,{"Data"}),
Expand = Table.ExpandTableColumn(RC, "Partitioned", {"Category", "Sales", "Index"}, {"Category", "Sales", "Index"})
in
Expand