create index from existing data with duplicates

[Anonymous]

Hi folks, Can anyone help me with this one? I have a column of data which includes duplicates which I want to add a sequence number to so they become unique. e.g. F01 F01 F02 F03 would become F01-1 F01-2 F02-1 F03-1 I can think of a long-winded way to do it but I suspect there is an easy way.

[amitchandak · Accepted solution]

@Anonymous , first of add index in power query

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

then create a new column in dax like this

New column = [Column1] & " - " & countx(filter(Table, [Column1] = earlier([Column1]) && [index] <= earlier([index])),[Column1])

assume Column1 is the column having F01 F01 F02 F03

[Anonymous]

Is there a way to do this in M Code in Excel?

[Anonymous]

I did manage to do this by splitting into columns using a delimiter and then unpivot command.