Forum Discussion
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
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
Microsoft Employee
Microsoft EmployeeHere 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