Forum Discussion
PowerQuery sort and index on subgroup
Hi,
I want to sort the data on a table with creating an index and subindex column. The original data table is similar to the following layout, but with more customer and SKUs.
| Customer | Date | SKU | Quantity | Price | |
| A | 1/1/2026 | X | 50 | 10 | |
| A | 1/1/2026 | X | 10 | 12 | |
| A | 1/1/2026 | X | 1 | 15 | |
| A | 1/5/2026 | X | 10 | 11 | |
| A | 1/5/2026 | X | 1 | 14 | |
| A | 1/5/2026 | X | 50 | 9 |
I had trouble using PowerQuery's groupby function to index and sort it to the following format.
I want to be able to index by Customer & Date & SKU, with the sub-index by sorted ascending Quantity. The table output I wanted is listed below. Any pointers would be appreciated!
| Customer | Date | SKU | Quantity | Price | Index by Quantity | Sub -index by Date | |
| A | 1/1/2026 | X | 50 | 10 | 3 | 1 | |
| A | 1/1/2026 | X | 10 | 12 | 2 | 1 | |
| A | 1/1/2026 | X | 1 | 15 | 1 | 1 | |
| A | 1/5/2026 | X | 10 | 11 | 2 | 2 | |
| A | 1/5/2026 | X | 1 | 14 | 1 | 2 | |
| A | 1/5/2026 | X | 50 | 9 | 3 | 2 |
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyAzIjgNjUACRmoBSrg1XaECxthFMahE2RZE0xNRvilAZhE1yyYIdZKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, SKU = _t, Quantity = _t, Price = _t]), #"Grouped Rows" = Table.Group(Source, {"Customer", "Date", "SKU"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(Table.SelectColumns(_,{"Quantity","Price"}),{"Quantity",Order.Descending}), "Quantity Index", 1, 1, Int64.Type)}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Date Index", 1, 1, Int64.Type), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"Quantity", "Price", "Quantity Index"}, {"Quantity", "Price", "Quantity Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Quantity", Int64.Type}, {"Price", Currency.Type}, {"Quantity Index", Int64.Type}, {"Date", type date}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
4 Replies
This is very expensive to do in Power Query as it breaks the streaming. Can you do that in Power BI instead?
Would like to use the sub-index column to pivot the table, the row data of Quantity and Price into columns.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyAzIjgNjUACRmoBSrg1XaECxthFMahE2RZE0xNRvilAZhE1yyYIdZKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, SKU = _t, Quantity = _t, Price = _t]), #"Grouped Rows" = Table.Group(Source, {"Customer", "Date", "SKU"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(Table.SelectColumns(_,{"Quantity","Price"}),{"Quantity",Order.Descending}), "Quantity Index", 1, 1, Int64.Type)}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Date Index", 1, 1, Int64.Type), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"Quantity", "Price", "Quantity Index"}, {"Quantity", "Price", "Quantity Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Quantity", Int64.Type}, {"Price", Currency.Type}, {"Quantity Index", Int64.Type}, {"Date", type date}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Paste the code below into the advanced editor to see a method of doing this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyAzIjgNjUACRmoBSrg1XaECxthFMahE2RZE0xNRvilAZhE1yyYIdZKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, SKU = _t, Quantity = _t, Price = _t]), #"Grouped Rows" = Table.Group(Source, {"Customer"}, { {"Added Index Columns",(t)=> [a=Table.AddIndexColumn(t,"Index",0,1, Int64.Type), //enable original order restore b=Table.AddRankColumn(a,"Quantity Index", {"Quantity", Order.Ascending}, [RankKind=RankKind.Dense]), c=Table.AddRankColumn(b,"Date Sub-Index", {"Date", Order.Ascending},[RankKind=RankKind.Dense]), d=Table.Sort(c,{"Index",Order.Ascending}) //Return to original Order ][d], type table [Customer=nullable text, Date=nullable date, SKU=nullable text, Quantity=nullable number, Price=nullable number, Quantity Index=Int64.Type, #"Date Sub-Index"=Int64.Type] }}), #"Expanded Added Index Columns" = Table.ExpandTableColumn(#"Grouped Rows", "Added Index Columns", {"Date", "SKU", "Quantity", "Price", "Quantity Index", "Date Sub-Index"}) in #"Expanded Added Index Columns"Original Data:
Results
