Forum Discussion
Generate incremental number in groups
... 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"
Sorry the code got trimmed off
let
// Make a Age Range, Max number is 127 else if needed more you must change data type from Int8 to Int16
Source = {-1.. 127},
// Convert List to table
ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Rename field to [Age]
RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "Age"}}),
// Chnage the data type of the field [Age] to Int8 (-127 ... +127)
ChangeDataType= Table.TransformColumnTypes(RenamedColumns ,{{"Age", Int8.Type}}),
// Add AgeBand1
AddedAgeBand1Column = Table.AddColumn(ChangeDataType, "AgeBand1", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 18 then "Under 18"
else if 18 <= [Age] and [Age] <= 24 then "[18-24]"
else if 25 <= [Age] and [Age] <= 34 then "[25-34]"
else if 35 <= [Age] and [Age] <= 44 then "[35-44]"
else if 45 <= [Age] and [Age] <= 54 then "[45-54]"
else if 55 <= [Age] and [Age] <= 64 then "[55-64]"
else if 65 <= [Age] and [Age] <= 74 then "[65-74]"
else if 75 <= [Age] and [Age] <= 84 then "[75-84]"
else if 85 <= [Age] and [Age] <= 94 then "[85-94]"
else if 95 <= [Age] and [Age] <= 104 then "[95-104]"
else if 105 <= [Age] and [Age] <= 114 then "[105-114]"
else if 115 <= [Age] and [Age] <= 124 then "[115-124]"
else "[125 +"),
// Add AgeBand2
AddedAgeBand2Column = Table.AddColumn(AddedAgeBand1Column, "AgeBand2", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 20 then "Under 20"
else if 20 <= [Age] and [Age] <= 29 then "[20-29]"
else if 30 <= [Age] and [Age] <= 39 then "[30-39]"
else if 40 <= [Age] and [Age] <= 49 then "[40-49]"
else if 50 <= [Age] and [Age] <= 59 then "[50-59]"
else if 60 <= [Age] and [Age] <= 69 then "[60-69]"
else if 70 <= [Age] and [Age] <= 79 then "[70-79]"
else if 80 <= [Age] and [Age] <= 89 then "[80-89]"
else if 90 <= [Age] and [Age] <= 99 then "[90-99]"
else if 100 <= [Age] and [Age] <= 109 then "[100-109]"
else if 110 <= [Age] and [Age] <= 119 then "[110-119]"
else "[120 +")
in
AddedAgeBand2Column
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.
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.