cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sperry Regular Visitor
Regular Visitor

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??

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Encode a Text field

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




1 REPLY 1
Super User
Super User

Re: Encode a Text field

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries