Forum Discussion

tiramisu83's avatar
tiramisu83
New Member
4 years ago
Solved

How to replace last 4 characters with 'X'?

Hi, I have a field name consist of customer name, and i would like to mask the name by replacing last 4 charcters with X, in power query editor?   Example: CHIRSTINE ANNABELLE > CHRISTINE ANNABXXXX...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Insert below step

    = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})

     See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsxJzldIS0vJTklTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FULLNAME2 = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"FULLNAME2", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})
in
    Custom1

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Use this in a custom column where Data is column name

 

= if Text.Length([Data])<=4 then "XXXX" else Text.ReplaceRange([Data], Text.Length([Data])-4, 4,"XXXX")

OR

= Text.Combine(List.RemoveLastN(Text.ToList([Data]),4),"")&"XXXX"

 

tiramisu83's avatar
tiramisu83
New Member
4 years ago

Hi Vijay,

 

Thanks for your help..but i still have some error when applying this formula in Power Query Editor

 

For case 1 - add a new column called FULLNAME2

= Table.AddColumn(#"Changed Type1", "FULLNAME2", each Text.Combine(List.RemoveLastN(Text.ToList([FULL_NAME]),4),"")&"XXXX") >> This formula works fine!

 

If I don't want to add a new column, but to "update" FULL_NAME instead (means directly apply the logic in FULL_NAME column itself), what's the formula? I have found something like Table.ReplaceValue but not sure how the correct syntax..

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Insert below step

    = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})

     See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsxJzldIS0vJTklTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FULLNAME2 = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"FULLNAME2", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})
in
    Custom1