Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

how to replace all special character with space

replace all the special character in a column containing sentences with white space

 

input 

hi.you/love

 

output

hi you love

Need Help
Show and Tell
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You need to have an approach where you need to replace all other characters other than allowed. In below approach, a to z and 0 to 9 are allowed. Hence, it will replace any other character encountered

    {"A".."Z","a".."z","0".."9"} - If you feel 0 to 9 not allowed, make it {"A".."Z","a".."z"}

    So basically you are taking Whitelist approach rather than Blacklist approach as creating Blacklist is a big task and also prone to error.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjU89TPyS9Lja/ML1WK1YlW8q10yEvMTVXNLAZzwzNSi1JjSg0MjMwSi1K1gaoU0/Mz89LBko4pKUWpxcUKmcUKxiY6Cs75OTmZeUB9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    BuffListAllowedChars = List.Buffer({"A".."Z","a".."z","0".."9"}),
    SourceList = List.Buffer(Source[Column1]),
    ListCount = List.Count(SourceList),
    GenList = List.Generate(()=>[x=Text.Combine(List.Transform(Text.ToList(SourceList{0}),(a)=>if List.Contains(BuffListAllowedChars,a) then a else " ")),i=0], each [i]<ListCount, each [i=[i]+1, x=Text.Combine(List.Transform(Text.ToList(SourceList{i}),(a)=>if List.Contains(BuffListAllowedChars,a) then a else " "))], each [x]),
    Result = Table.FromColumns({GenList},{"Result"}) 
in
    Result

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here is one way to do it. Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. This approach updates the values in an existing column. You could also take the code from the Custom1 step and create a new column with it, then delete the original.

     

    let
        Source =
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText(
                            "i45WysjUq8wv1c/JL0tVio0FAA==",
                            BinaryEncoding.Base64
                        ),
                        Compression.Deflate
                    )
                ),
                let
                    _t = ((type nullable text)
                    meta
                    [
                        Serialized.Text = true
                    ])
                in
                    type table [TextColumn = _t]
            ),
        #"Changed Type" =
            Table.TransformColumnTypes(
                Source,
                {
                    {
                        "TextColumn",
                        type text
                    }
                }
            ),
        Custom1 =
            Table.TransformColumns(
                #"Changed Type",
                {
                    "TextColumn",
                    each
                        Text.Combine(
                            List.ReplaceMatchingItems(
                                Text.ToList(_),
                                List.Transform(
                                    {
                                        ".",
                                        "/"
                                    },
                                    each
                                        {
                                            _,
                                            " "
                                        }
                                )
                            ),
                            ""
                        )
                }
            )
    in
        Custom1

     

    Pat

     

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    You need to have an approach where you need to replace all other characters other than allowed. In below approach, a to z and 0 to 9 are allowed. Hence, it will replace any other character encountered

    {"A".."Z","a".."z","0".."9"} - If you feel 0 to 9 not allowed, make it {"A".."Z","a".."z"}

    So basically you are taking Whitelist approach rather than Blacklist approach as creating Blacklist is a big task and also prone to error.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjU89TPyS9Lja/ML1WK1YlW8q10yEvMTVXNLAZzwzNSi1JjSg0MjMwSi1K1gaoU0/Mz89LBko4pKUWpxcUKmcUKxiY6Cs75OTmZeUB9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    BuffListAllowedChars = List.Buffer({"A".."Z","a".."z","0".."9"}),
    SourceList = List.Buffer(Source[Column1]),
    ListCount = List.Count(SourceList),
    GenList = List.Generate(()=>[x=Text.Combine(List.Transform(Text.ToList(SourceList{0}),(a)=>if List.Contains(BuffListAllowedChars,a) then a else " ")),i=0], each [i]<ListCount, each [i=[i]+1, x=Text.Combine(List.Transform(Text.ToList(SourceList{i}),(a)=>if List.Contains(BuffListAllowedChars,a) then a else " "))], each [x]),
    Result = Table.FromColumns({GenList},{"Result"}) 
in
    Result