how to replace all special character with space

[dhruvgulati]

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

input 

hi.you/love

output

hi you love

[Vijay_A_Verma · Accepted solution]

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

[mahoneypat]

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