Forum Discussion

coffeexyz's avatar
coffeexyz
Frequent Visitor
7 months ago
Solved

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 Dat...
Need Help
  • lbendlin's avatar
    lbendlin
    7 months ago
    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.

ronrsnfld's avatar
ronrsnfld
Super User
7 months ago

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