How to replace last 4 characters with 'X'?

[tiramisu83]

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

May i know which function i can use?

Sorry, this is my first time using Power BI and I have a lot of thing still need to learn from you all 🙂

Hope someone can help me on this.

Thanks!!

[Vijay_A_Verma]

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]

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 · Accepted solution]

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