create a record number column for each person id

[hpatel24779]

Hi All,

I have tried using index but not what i need. I want to create a new column with a record number starting from 0001 to 1300 if i have 1300 unique people in my data. For each id the record number increases by 1.

Example of what i require:

Person ID	Record Number
56463	0001
46979	0002
1234	0003
69956	0004
69956	0004
4692	0005
4692	0005
15652	0006

i will ensure my data is sorted by person id to be on safe side.

Is this achievable?

kind regards

Hetal

[SamWiseOwl · Accepted solution]

Hi @hpatel24779 

Something like this?

Rank ID =

var r =

RANK(

    DENSE

    ,ORDERBY('Table'[Person ID], ASC) --rank by personID, DENSE keeps all numbers

    ,LAST --missing ranked last

)

RETURN

    REPT(0,4 - len(r)) & r --add 0s equal to 4 minus the number of characters in the rank 

[hpatel24779]

Hi @SamWiseOwl 

exactly what i want. can this be done in power query?

kind regards

Hetal

[SamWiseOwl]

I don't suppose you are using the online version...

https://learn.microsoft.com/en-us/power-query/rank-column