Power Query to remove or select Special Characters or Text or Numbers from a Alpha Numeric String in Power BI

How to remove or select Numbers or Text or Special Characters from a Alpha Numeric String using Power Query in Power BI

Scenario:

Suppose we have an column having the values with combination of Alpha Numeric and special characters as shown below. From this AlphaNumSpecialString Column we need to remove or select the Numbers or Text or Special Characters.

This Scenario can be achieved using the Text Functions of the Power Query as explained below examples:

1) Select AlphaNumeric, Text, Numbers, Special Characters

let
Source = Excel.Workbook(File.Contents(“E:\Tech_Lab\Power BI\DataSets\ds_Sample_Data.xlsx”), null, true),
ds_Strings_Sheet = Source{[Item=”ds_Strings”,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(ds_Strings_Sheet, [PromoteAllScalars=true]),
SelectAlphaNum = Table.AddColumn(#”Promoted Headers”, “AlphaNumeric”, each Text.Select([AlphaNumSpecialString],{“A”..”z”,”0″..”9″})),
SelectText = Table.AddColumn(SelectAlphaNum, “TextString”, each Text.Select([AlphaNumSpecialString],{“A”..”z”})),
SelectNumeric = Table.AddColumn(SelectText, “SelectNumeric”, each Text.Select([AlphaNumSpecialString],{“0”..”9″})),
SelectSpecial = Table.AddColumn(SelectNumeric, “SelectSpecial”, each Text.Remove([AlphaNumSpecialString],{“A”..”z”,”0″..”9″})),
CleanField = Table.AddColumn(SelectSpecial, “CleanField”, each Text.Clean([AlphaNumSpecialString]))
in
CleanField

Result:

Notes:

The Power BI , Transform > Clean option will not remove any of the special characters that discussed above.

The characters like ^ (carrot), _ (underscore), ‘(hyphon) and \(back slash) will be treated as the Strings instead of special characters.

2) Select Lower Case, Upper Case letters, Vowels :

let
Source = Excel.Workbook(File.Contents(“E:\Tech_Lab\Power BI\DataSets\ds_Sample_Data.xlsx”), null, true),
ds_Strings_Sheet = Source{[Item=”ds_Strings”,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(ds_Strings_Sheet, [PromoteAllScalars=true]),
SelectLC = Table.AddColumn(#”Promoted Headers”, “txtLowerCase”, each Text.Select([AlphaNumSpecialString],{“a”..”z”})),
SelectUC = Table.AddColumn(SelectLC, “TXT_UpperCase”, each Text.Select([AlphaNumSpecialString],{“A”..”Z”})),
SelectVowels = Table.AddColumn(SelectUC, “SelectVowels”, each Text.Select([AlphaNumSpecialString],{“a”,”e”,”i”,”o”,”u”,”A”,”E”,”I”,”O”,”U”}))
in
SelectVowels

Result:

3) Count AlphaNumeric, Text, Numbers, Special Characters, Vowels :

let
Source = Excel.Workbook(File.Contents(“E:\Tech_Lab\Power BI\DataSets\ds_Sample_Data.xlsx”), null, true),
ds_Strings_Sheet = Source{[Item=”ds_Strings”,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(ds_Strings_Sheet, [PromoteAllScalars=true]),
CountAlphaNum = Table.AddColumn(#”Promoted Headers”, “CountAlphaNumeric”, each Text.Length(Text.Select([AlphaNumSpecialString],{“A”..”z”,”0″..”9″}))),
CountText = Table.AddColumn(CountAlphaNum, “CountTextString”, each Text.Length(Text.Select([AlphaNumSpecialString],{“A”..”z”}))),
CountSpecial = Table.AddColumn(CountText, “CountSpecial”, each Text.Length(Text.Remove([AlphaNumSpecialString],{“A”..”z”,”0″..”9″}))),
CountVowels = Table.AddColumn(CountSpecial, “CountVowels”, each Text.Length(Text.Select([AlphaNumSpecialString],{“a”,”e”,”i”,”o”,”u”,”A”,”E”,”I”,”O”,”U”})))
in
CountVowels

Result: