
Usage scenarios:
Sample data table:
#1. Check field if they existed English characters.(upper: 65 ~ 90, lower: 97 ~ 122)
- Create a list of all English characters.
List.Transform(List.Union({{65..90},{97..122}}),each Character.FromNumber(_)) - 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)
- Create the list of all common Chinese character.
List.Transform({19968..40869},each Character.FromNumber(_)) - 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)
- Create a list of all full width English characters.
List.Transform(List.Union({{65313 ..65338},{65345 ..65370}}),each Character.FromNumber(_)) - 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
Be the first to comment