Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to Solution.
@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)
)
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
@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)
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |