Hi there,
I'm trying to add a sub-index to a column, I have gotten it so far below but cannot work out how to increment the 'Upper' column by 1 so that looking at the 'Tasks' column where it is in uppercase is the beginning of the index. So where there is an uppercase the 'Upper' column begins at 1 then each below should be 2, 3 etc & where the next uppercase row is, it reverts back to 1 then the numbering sequence begins again. Sample below of current & expected result.
Hoping someone will be able to assist me 🙂
Current
Result
Solved! Go to Solution.
NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},0},(x,y)=>let a=if Text.Remove(y[Tasks],{"a".."z"})=y[Tasks] then 1 else x{1}+1 in {x{0}&{y&[Upper=a]},a}){0})
NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},0},(x,y)=>let a=if Text.Remove(y[Tasks],{"a".."z"})=y[Tasks] then 1 else x{1}+1 in {x{0}&{y&[Upper=a]},a}){0})