
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.
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:
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:
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:
Be the first to comment