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

 

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

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

     

3 Replies

  • 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