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]

.

.

 

How can i generate an extra column using M or DAX to make a SortColumn like ....

price     pricebracket     SortOrder

1          Under 1000            1

2          Under 1000            1

3           Under 1000           1

4           Under 1000            1

.

.

1001      [01  -2  K]             2

1002      [01  -2  K]             2

.

.

i need it to be Automaticly generated becasue the $ range can be anything imported from a data source like SQL

thanks

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

9 Replies

  • 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