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

SNik's avatar
SNik
Helper I
10 years ago

OK i just tested 2 age band and i need 2 extra [Group Sort Order] field, one for each AgeBand

so that is toooooo much work, i am going to assume that in the future they will fix this issues and come up with a more simple solution.

ImkeF's avatar
ImkeF
Community Champion
10 years ago

Thanks Nik,

starting to get the picture, but not fully there yet.

 

But you might want to look into these features:

List.Generate lets you generate a list according to the different rules per AgeBand. This article explains how you apply it: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/

 

Expression.Evaluate: Might be an alternative if List.Generate doesn't deliver or to feed in the rules from a table. This is the only article I'm aware of: https://bondarenkoivan.wordpress.com/2016/01/25/rename-columns-of-nested-tables-in-power-query/

 

You don't have to create them as additional columns manually. Just create them in one column with an added column containing their name and pivot on it at the end.

 

Not sure if this will work at the end, but definitely some more options to look into.