Forum Discussion
telesforo1969
Helper V
1 year agoExtract alphanumeric words from a Power Query text string
I need help extracting alphanumeric words that begin with the number 0 and end with a space. They can be 6, 7, 8, or 9 characters long, but are delimited by a space at the end. Likewise, when multipl...
- 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.
- 1 year 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.
telesforo1969
Helper V
1 year agoThank you very much. I'll implement it and let you know.
telesforo1969
Helper V
1 year ago
It repeats rows based on the words found (5), what could I be doing wrong?