Forum Discussion

Cevola's avatar
Cevola
Helper I
1 year ago
Solved

Pseudonymizing the personal data

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

Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    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"

6 Replies

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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's avatar
    PwerQueryKees
    Super User
    1 year ago

    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's avatar
      Cevola
      Helper I
      1 year ago

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

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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