Forum Discussion
Solved
Clear Field with non alpha numeric characters
Some of the data I am importing in my query is using a different alphabet so just appearing in the import as a random selection of special characters in the fields. How do I clear the fields to null...
The pedestrian way would be
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOjz9UcOcw9MVgHTLosPTD004PP3oZBCvYQaQtUMBKg/kgplAJUf3AZWtgKmfqxSrE62Ul5+XqpCfplCSkViiFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], Character.FromNumber(215)) then true else false) in #"Added Conditional Column"The cool way is
https://community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/td-p/932611
How do I change the ingestion? The excel sheet that the query is picking up from has the characters in anyway.
Is there a way of saying if it is not A...Z or a...z then delete it?
The pedestrian way would be
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOjz9UcOcw9MVgHTLosPTD004PP3oZBCvYQaQtUMBKg/kgplAJUf3AZWtgKmfqxSrE62Ul5+XqpCfplCSkViiFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], Character.FromNumber(215)) then true else false)
in
#"Added Conditional Column"
The cool way is
https://community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/td-p/932611