Encode a Text field

[sperry]

Hi - I have a government issued unique identifer (called NHI in New Zealand). Its in the format ABC1234.

I have a data set that I would like to pseudonymise using a formula in query editor as I would like to remove the original NHI column so it is not visible to users.

I have tried using the text coding functions in power query but all return an error.

I have also tried creating a table of unique NHI values using the summarize function and creating a an index number using rankx but if I do this I cannot remove the NHI column.

Any suggestions??

[ImkeF · Accepted solution]

You can create a lookup-table in the query editor where you do the things that you tried to achieve in DAX:

- reference your original table and select just the NHI-column

- remove duplicates

- add index column

That's your lookup-table now. Create a NEW query (!) where you reference your original table, merge with the new lookup-table on NHI, expand the index (your new key) and remove the NHI-column. Disable load to datamodel for your original table.