Forum Discussion

BeginnerBI's avatar
BeginnerBI
Helper I
3 years ago
Solved

Mask specific types partially

SOS!  SOS!  SOS!  SOS!  SOS!  SOS!

 

Scenario: Column Type have these codes: (1,5,7,9,12,14,55) 

 

My boss want me to mask the social and the id but partially only for the code 1,14,55 

 

Sample (I'm using the same num just for this demo)

NOW

  1 |       976-46-8465      |   7593457345

  5 |       477-47-8489      |   5776875456

  7 |       711-46-4644      |   0545646445

14 |       885-78-4465      |   7864214444

 

with the Mask should look like this

 

  1 |       xxxx-xx-8465      |   xxxxxxxxx45

  5 |       477-47-8489      |   5776875456

  7 |       711-46-4644      |   0545646445

14 |       xxxx-xx-4465      |   xxxxxxxxx44

 

What I need to do?

Data Protection
Show and Tell
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    BeginnerBI Maybe:

    Measure = 
  VAR __Value = MAX('Table'[Value])
  VAR __Type = MAX('Table'[Type])
RETURN
  IF( 
    __Type <> 1 && __Type <> 14 && __Type <> 55,
    __Value,
    "xxx-xx-" & RIGHT(__Value,4)
  )

Measure 1 = 
  VAR __Value = MAX('Table'[Value1])
  VAR __Type = MAX('Table'[Type])
RETURN
  IF( 
    __Type <> 1 && __Type <> 14 && __Type <> 55,
    __Value & "",
    "xxxxxxxx" & RIGHT(__Value,2)
  )

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    BeginnerBI Maybe:

    Measure = 
  VAR __Value = MAX('Table'[Value])
  VAR __Type = MAX('Table'[Type])
RETURN
  IF( 
    __Type <> 1 && __Type <> 14 && __Type <> 55,
    __Value,
    "xxx-xx-" & RIGHT(__Value,4)
  )

Measure 1 = 
  VAR __Value = MAX('Table'[Value1])
  VAR __Type = MAX('Table'[Type])
RETURN
  IF( 
    __Type <> 1 && __Type <> 14 && __Type <> 55,
    __Value & "",
    "xxxxxxxx" & RIGHT(__Value,2)
  )
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , BeginnerBI 

    It can be realized in Power Query Editor.Here are the steps you can refer to :
    You can put this in your Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY3BDcAwCAN34R0etAaTWaLsv0ZBzaNSkXj4bPBaYjJkMhShifBS9HnDWSt7LGkEUsEK5Czl8EDg+OwTs37Q8Odbo0xXpuI0ZOAy1LwN/4pvYj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NUMBER = _t, NO = _t]),
    test = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NUMBER", type text}, {"NO", Int64.Type}}),
    Custom1 = Table.AddColumn(test,"NUMBER2",(x)=> if List.Contains({1,14,55},x[ID]) then Text.ReplaceRange(Text.ReplaceRange(x[NUMBER],0,3,"xxx"),4,2,"xx")   else x[NUMBER]   ),
    Custom2 = Table.AddColumn(Custom1,"NO2",(x)=>  if List.Contains({1,14,55},x[ID]) then Text.ReplaceRange(Text.From(x[NO]),0,8,"xxxxxxxx")    else Text.From(x[NO])  )
in
    Custom2

    Then we can meet your need , the result is as follows:

     

     

    Best Regards,

    Aniya Zhang

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