Masked Member Health ID

[Cmacvp16]

I want to use Power Query to take a member "Health ID" number and create a new column of "Masked ID" to hide their identity. Use 4 digits and increment by 1.

Health ID Masked ID
12345678911 0001
12345678911 0001
33333333333 0002
45454545454 0003
33333333333 0002
45454545454 0003

 

[Cmacvp16]

One way I solved this problem was to create a query on Health ID and group by the Health ID. This gave me a list of unique IDs. Using Power Query I added an index starting at 100 and incrementing by 1.

Next I merged the medical data query I originally pulled that had 1000 records to add in the Index column. I renamed the Index column "Masked ID".

Is there a way to add a column using Health ID and creating Masked ID directly in the original data file?