cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tiramisu83
New Member

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

 

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!!

1 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

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

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"

 

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..

 

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

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors