Unique anonymize customer names in two different table with Relationship

[klikk680]

I would need to mask the customer identify, it is fiscal code.  This information is presents in two different tables.

The fiscal code field is also the key to relating the tables so, the new field anonymized must maintain the relationship between the tables.

Any ideas how to create unique masked customer names with Power BI?

Thank you.

[Anonymous]

Hi @klikk680 ,

My sample data is this.

Table1

NameSales

Paul	1
Bob	2
Tom	3
James	4
Jimmy	5

Table2

NameDate

Paul	1/1/2020
Bob	2/2/2020
Tom	3/3/2020
James	4/4/2020
Jimmy	5/5/2020
Jimmy	6/6/2020

The way to keep anonymous names in the original relationship between the two tables is to ensure that they are not the same.

You could create a calculated column both in Table1 and Table2.

Anonymised = "Name " & RANKX('Table1',[Name],,ASC,Dense)

Anonymised = "Name " & RANKX('Table2',[Name],,ASC,Dense)

Or you could use spaces to distinguish.

Name     Anonymised Name
"Bob"    "Name xxxxxxx"
"James"  "Nmae xxxxxxx "
"Jimmy"  "Name xxxxxxx   "

Anonymised 2 = "Name XXXXXXX" & REPT(" ", RANKX('Table1',[Name],,ASC,Dense))

Anonymised2 = "Name XXXXXXX" & REPT(" ", RANKX('Table2',[Name],,ASC,Dense))

Even if the Name column is not used as the relationship, the anonymous column can still maintain the original relationship.

You can check more details from here.

Reference:How to anonymize/mask part of Data

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You can 

[klikk680]

hi

I tried but it doesn't work ... maybe I'm wrong?

https://drive.google.com/drive/folders/1J2sPjdt5IpHDis12Vq-KmznPMbTakSbZ?usp=sharing 

[amitchandak]

@klikk680 , refer

https://www.biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-par...

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

[Greg_Deckler]

@klikk680 - Would need to see sample data but maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Anonymous-Too/m-p/1106722#M533

[klikk680]

hi @Greg_Deckler 

this solution could be fine but "Anonymous Column" is to long 🤔

After your idea the result is:

Table1		Table2
CF	Cost	Anonymous CF by Anonymous Too Column		CF	doc	Anonymous CF by Anonymous Too Column
aaasss21x10y222h	2	50979711511511550509750494912048121104		aaasss21x10y222h	a21	50979711511511550509750494912048121104
pppddd12e55u999r	21	10149112112112100100100505353117575757114		yyyccc12e55u999r	a32	10149121121121999999505353117575757114
0000000000212365	1	48504948484848485048484848535154		0000000000212365	a11	48504948484848485048484848535154
1234567890123456	2	51504949485353575051545452525556		1234567890123456	a65	51504949485353575051545452525556
0000000000000002	3	48484848484848485048484848484848		0000000000000009	a33	48484848484848484848484848484857
2000000000000000	5	48484848484848485048484848484848		2000000000000010	a68	48494848484848485048484848484848
pppddd12e55u999r	5	10149112112112100100100505353117575757114
1234567890123456	9	51504949485353575051545452525556

I will say something more about the field to be anonymized: the CF field is a combination of numbers and letters or it can be just numbers, its length is 16 characters and some values ​​may be missing.

Is there any way to get a anonymous value of 16 characters or less ?

Thanks,

[Greg_Deckler]

@klikk680 - Well you could use LEFT([Anonymous],16) but can't guarantee that would be unique. I'll try to think of something else but it is the unique part that is problematic I think.

Or turn off word wrap and shrink the column.