Clear Field with non alpha numeric characters

[joooffice]

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 that contain these special characters without having to specify every different type of character?

[lbendlin]

you have not specified the encoding used during ingestion.

You can look at the hex value of each character and decide if it is ANSI (0...127) or any of the Unicode starting bytes etc.  and then decide what to do in each case.

[joooffice]

Thanks for the reply.

I dont know what encoding was used during ingestion. The fields are like this in my original excel document as it is exported from a database which can cope with the other alphabet.

An example field is:

דניאל בן דב ומרים

I just want the query to find all the cells that containt these types of characters ie not A...Z and usual punctuation and clear them to null

[lbendlin]

You can use 0xC3 - that's the × in your text. 

Or - you could use UTF-8 for ingestion.

[joooffice]

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?

[lbendlin · Accepted solution]

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