Giving a number as an identifier of all text strings between two irregularly recurring text strings

[Milagros]

Hi, I have a column of data, that repeats text "a" at irregular inervals, I need to give a number to every "a" and the same number for every other text between "a" and the next "a", see below. The value of "a" should increase by 1 at every instance of "a". Hopefully there is a simple solution, which I am missing! 🙂 I tried creating a index, then using DAX, but haven't been able to solve it. 

Col               Number 

a                   1

b                   1

a                   2

b                   2

c                   2

a                   3

b                   3                

[ryan_mayu · Accepted solution]

@Milagros 

create an index column. then use below DAX to create another column

Count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column1]=EARLIER('Table'[Column1])&&'Table'[Index]<=EARLIER('Table'[Index])))

[Anonymous · Accepted solution]

Hi @Milagros 

You can try to use rankx function to build a calculated column.

Add an index column in Power Query Editor by index function.

Then build a number column.

Number = 
RANKX(FILTER('Table','Table'[Col]=EARLIER('Table'[Col])),'Table'[Index],,ASC)

Result is as below.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Milagros 

create an index column. then use below DAX to create another column

Count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column1]=EARLIER('Table'[Column1])&&'Table'[Index]<=EARLIER('Table'[Index])))