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.

lbendlin's avatar
lbendlin
Super User
7 months ago

This is very expensive to do in Power Query as it breaks the streaming.  Can you do that in Power BI instead?

coffeexyz's avatar
coffeexyz
Frequent Visitor
7 months ago

Would like to use the sub-index column to pivot the table,  the row data of Quantity and Price into columns. 

  • lbendlin's avatar
    lbendlin
    Super User
    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.