Forum Discussion

aignn's avatar
aignn
Icon for Helper II rankHelper II
5 years ago
Solved

How to anonymize names in table?

Hi!

 

I have table with names:

John Doe

Mary James

Johny Depp

Pater Griffin

John Doe

 

they can be repeated

 

I need anonymize names and bring them to this form:

John Doe > user-1

Mary James > user-2

Johny Depp > user-3

Pater Griffin > user-4

John Doe > user-1

 

This is possible?

Thank you advanced

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi aignn 

    Sample PBIX file here.

    Yes, Group By Name keeping all rows.  Then add an index column starting from 1 and add a Custom Column joining the real name and the index number.  Expand the rows again

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9VitWJVvJNLKpU8ErMTS0Gc0FyeZUKLqkFBWB+QGpJapGCe1FmWlpmHlwFRHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table [Name=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each "user-" & Text.From([Index])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Count", {"Name"}, {"Name.1"})
in
    #"Expanded Count"

     

    Regards

    Phil

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi aignn 

    Sample PBIX file here.

    Yes, Group By Name keeping all rows.  Then add an index column starting from 1 and add a Custom Column joining the real name and the index number.  Expand the rows again

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9VitWJVvJNLKpU8ErMTS0Gc0FyeZUKLqkFBWB+QGpJapGCe1FmWlpmHlwFRHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table [Name=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each "user-" & Text.From([Index])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Count", {"Name"}, {"Name.1"})
in
    #"Expanded Count"

     

    Regards

    Phil