cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EimearC
Frequent Visitor

Add sub-index to column

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

EimearC_0-1680220975041.png

Result

EimearC_1-1680221005394.png

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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})

View solution in original post

2 REPLIES 2
EimearC
Frequent Visitor

Thank you so much @wdx223_Daniel  Works perfectly!

wdx223_Daniel
Super User
Super User

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})

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors