Pseudonymizing the personal data

[Cevola]

Hello all,

I have 2 columns with personal data in my table(Employee ID and Badge ID). I need to provide data to the customer but I cannot share these 2 columns as they contain personal data.

How can I pseudonymize these 2 columns either seperately or together. Also to be noted the ps

Thanks

[Anonymous]

Hi @Cevola 

Did the solution PwerQueryKees  offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other questions you can offer some information so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[PwerQueryKees · Accepted solution]

Here it is:

Original table:

The indexes created. For this sample it runs 1:1, but I assume that in the actual data the same combination of employee id and badge id will occur multiple times:

The merge:

The end result:

All queries:

// Original Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Id", Int64.Type}, {"Start Location", type text}, {"End Location", type text}, {"Start Time", type datetime}, {"End Time", type datetime}, {"Day of the Week", type text}, {"Duration", type time}, {"Badge ID", Int64.Type}})
in
    #"Changed Type"

// Indexes
let
    Source = #"Original Table",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Employee Id", "Badge ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// End Result
let
    Source = #"Original Table",
    #"Merged Queries" = Table.NestedJoin(Source, {"Employee Id", "Badge ID"}, Indexes, {"Employee Id", "Badge ID"}, "Indexes", JoinKind.LeftOuter),
    #"Expanded Indexes" = Table.ExpandTableColumn(#"Merged Queries", "Indexes", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Indexes",{"Employee Id", "Badge ID"})
in
    #"Removed Columns"

[PwerQueryKees]

And replacing these with a unique number per combination of employee id and badge id will meet your goal?

[Cevola]

yes

[PwerQueryKees]

Hard to judge whether it meets your use case, but I would do something in PowerQuery like:

• Make a reference to the base table
• Remove all columns except the two sensitive columns
• Remove duplicates
• Add an index
• Make a new querey referencing the base table
• merge the indexed table on the 2 columns
• Expand the index column
• Remove the sensistive columns

Share a sample of your data and a sample of the desired output if you would like me to give it a try....

[Cevola]

I want to share this data but I want to pseudonymize the employee id and badge id as they contain personal information