Forum Discussion
Encode a Text field
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??
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.
1 Reply
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.