Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
BeginnerBI
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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)
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1666835719797.png

 

 

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

Greg_Deckler
Super User
Super User

@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)
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.