Forum Discussion

joooffice's avatar
joooffice
Helper I
6 years ago
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 that contain these special characters without having to specify every different type of character?

 

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      joooffice
      Helper I
      6 years ago

      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's avatar
        lbendlin
        Super User
        6 years ago

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

         

        Or - you could use UTF-8 for ingestion.