Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Usage scenarios:
Sample data table:
#1. Check field if they existed English characters.(upper: 65 ~ 90, lower: 97 ~ 122)
#"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)
#"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)
#"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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.