Forum Discussion
Extract alphanumeric words from a Power Query text string
- 1 year ago
Given your example, you merely have to split Description by space or comma, and then select any word that begins with "0".
let //change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}, {"Description", type text}}), #"Extract Descriptors" = Table.AddColumn(#"Changed Type","Value", (r)=> [a=Text.SplitAny(r[Description],", "), b=List.Select(a, each Text.StartsWith(_,"0"))][b], type {text}), #"Expanded Value" = Table.ExpandListColumn(#"Extract Descriptors", "Value") in #"Expanded Value"If your posted sample is not truly representative, you might need to add tests for length of the word, inclusion of the hyphen, inclusion of alphabet characters, etc.
- 11 months ago
The code is meant to be pasted into the Advanced Editor all by itself, NOT into the custom column dialog. And you must change the Source line to reflect your actual data source.
Given your example, you merely have to split Description by space or comma, and then select any word that begins with "0".
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}, {"Description", type text}}),
#"Extract Descriptors" = Table.AddColumn(#"Changed Type","Value", (r)=>
[a=Text.SplitAny(r[Description],", "),
b=List.Select(a, each Text.StartsWith(_,"0"))][b], type {text}),
#"Expanded Value" = Table.ExpandListColumn(#"Extract Descriptors", "Value")
in
#"Expanded Value"
If your posted sample is not truly representative, you might need to add tests for length of the word, inclusion of the hyphen, inclusion of alphabet characters, etc.
I'm doing something simple wrong, I just compiled your code and expanded the table.
- ronrsnfld1 year ago
Super User
Hard to know without seeing your code. But I suspect you are not using the code I provided correctly. Can you copy/paste the M-Code you are actually using into a code block here so I can look at it?
- telesforo19691 year ago
Helper V
I'll send you a code.
=let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}, {"Description", type text}}),
#"Extract Descriptors" = Table.AddColumn(#"Changed Type","Value", (r)=>
[a=Text.SplitAny(r[Description],", "),
b=List.Select(a, each Text.StartsWith(_,"0"))][b], type {text}),#"Expanded Value" = Table.ExpandListColumn(#"Extract Descriptors", "Value")
in
#"Expanded Value"- ronrsnfld11 months ago
Super User
The code is meant to be pasted into the Advanced Editor all by itself, NOT into the custom column dialog. And you must change the Source line to reflect your actual data source.