Power BI: How to replace unique user ID with another

Question

I have a table with usage statistics and in the data as a unique user ID serves the phone number of the users.
I would need to replace this with a random user ID so the data gets anonymised.

Sample data:

date ID message place
2020-01-01 123456 "Some long text here" "London"
2020-01-02 123456 "Some more long text" "London"
2020-01-02 332545 "Some text" "Paris"
2020-01-14 123456 "Some text" "London"

So I would need to replace the ID columns unique values with a random ID, say 1 for 123456 and 2 for 332545.

Then I could delete the phone number column and publish the table in the internet as it shouldn't contain any sensitive information anymore.

What I have tried:
I copied the table, removed all columns except the User ID column. I then removed duplicates and created an index column.
The idea was that this would be the new User ID.
I tried to connect the two tables with "Manage relationship" button but I get a rejection.
I think I would need a relationship of many to one, if I chose my first table first and my second table second. But I get always a Many to many relationship for some reason.

In fact I would need to replace the value in the first table with the new ID in the second table.

Is there an easier way to do that?

Answer 1

Hi @PomPom ,

Please note that Power BI is currently not supported in the Q&A forums, the supported products are listed over here https://learn.microsoft.com/en-us/answers/products.

You may ask the experts in the dedicated Power BI forum over here:
https://community.powerbi.com/t5/Forums/ct-p/PBI_Comm_Forums

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Best regards,
Leon