Some filter operations based on character functions

Usage scenarios:

Sample data table:

 

#1. Check field if they existed English characters.(upper: 65 ~ 90, lower: 97 ~ 122)

 

1. Create a list of all English characters.
   List.Transform(List.Union({{65..90},{97..122}}),each Character.FromNumber(_))​
2. Add a custom column with function to compare each characters.
   

   #"Added Custom" =

   Table.AddColumn(

   #"Changed Type",

   "Check ABC",

   each

   List.IsEmpty(

   List.Select(

   Text.ToList([Desc]),

   each

   List.Contains(

   List.Transform(List.Union({{65..90},{97..122}}),each Character.FromNumber(_)),

   _

   )

   )

   )

   = false

   )

    

 

#2. Remove Chinese characters.(19968~40869)

 

1. Create the list of all common Chinese character.
   List.Transform({19968..40869},each Character.FromNumber(_))​
   
2. Add a custom column with function to compare each characters.
   

   #"Added Custom1" =

   Table.AddColumn(

   #"Added Custom",

   "Remove CHN",

   each

   Text.Combine(

   List.Transform(

   Text.ToList(Text.Lower([Desc])),

   each

   if

   List.Contains(

   List.Transform(

   {19968..40869},

   each Character.FromNumber(_)

   ),

   _

   )

   then

   null

   else

   _

   )

   )

   )
   ​

   
   

 

 

#3. Replace full width character to half width.(upper: 65313 ~ 65338, lower: 65345 ~ 65370)

 

1. Create a list of all full width English characters.
   List.Transform(List.Union({{65313 ..65338},{65345 ..65370}}),each Character.FromNumber(_))​
   
2. Add a custom column with function to compare each characters.
   

   #"Added Custom2" =

   Table.AddColumn(

   #"Added Custom1",

   "Replace full Width",

   each

   Text.Combine(

   List.Transform(

   Text.ToList([Desc]),

   each

   if

   List.Contains(

   List.Transform(List.Union({{65313 ..65338},{65345 ..65370}}),each Character.FromNumber(_)),

   _

   )

   then

   //65248 is the offset between full and half characters

   Character.FromNumber(Character.ToNumber(_) - 65248)

   else

   _

   )

   )

   )​

 

 

Summary:

For above samples, I shared to use characters function to achieve check field values, remove or replace specific characters. You can also combo with some other text function and use them to achieve fuzzy search or simple regular expressions.

 

Reference links: 

Character.FromNumber - PowerQuery M | Microsoft Docs 

Character.ToNumber - PowerQuery M | Microsoft Docs 

List of Unicode characters - Wikipedia 

 

 

Author: Xiaoxin

Reviewer: Ula Huang, Kerry Wang