Forum Discussion

SNik's avatar
SNik
Helper I
10 years ago
Solved

Generate incremental number in groups

Hi i have a sample table like price     pricebracket 1          Under 1000 2          Under 1000 3           Under 1000 4           Under 1000 . . 1001      [01  -2  K] 1002      [01  -2  K...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    ... editor eating my code again ...

     

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}),
        #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"})
    in
        #"Expanded AllRows"

  • ImkeF's avatar
    ImkeF
    10 years ago

    Only mildy amusing - all previews look fine and once posted randomly cut.

     

    Hopefully this link the the xlsx containing the M-code will survive: File

ImkeF's avatar
ImkeF
Community Champion
10 years ago

Hi Nik,

guessing that the steps speak for themselves:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"})
in
    #"Expanded AllRows"

 

 But otherwise just let me know where you need an explanation.

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    ... editor eating my code again ...

     

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}),
        #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"})
    in
        #"Expanded AllRows"

    • ImkeF's avatar
      ImkeF
      Community Champion
      10 years ago

      Only mildy amusing - all previews look fine and once posted randomly cut.

       

      Hopefully this link the the xlsx containing the M-code will survive: File

      • SNik's avatar
        SNik
        Helper I
        10 years ago

        Thank you for the answer, I just want to say that the if i have to add another Band Column (this happens alot in insurance company) then i will have to add another SortGroup Column for the second Band Column and etc...

        I don't think that is a good approch and the BI team should fix the issues that sorting a column must not depend on the preivious column, now i can add as much as new Banding field without change the sort or having an extra GroupSort field for every new Band Field. for me this is a limitation and something that i don't like to tell my client

         

        I hope that it was clear, anyways thank you again.