Extract alphanumeric words from a Power Query text string

[telesforo1969]

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 multiple words are found, separate them into rows, repeating the corresponding information (ID, Date, Description).

[ronrsnfld · Accepted solution]

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.

[v-venuppu]

Hi @telesforo1969 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @wdx223_Daniel @ronrsnfld @jgeddes for the prompt response.

The error happens because Excel 2016 Power Query doesn’t support the function Splitter.SplitTextByCharacterTransition (it only exists in newer versions like Excel 365 or Power BI Desktop).

In Excel 2016 you can still achieve the same result by:

Splitting your text column into words using spaces and punctuation as delimiters.Filtering the list so that only words starting with 0 remain.Cleaning those words so they only contain letters and digits.Expanding the results so each matching word appears in its own row.This way you’ll get the same outcome (e.g. from "test 1234 0123 ABC 0456 XYZ" you would see 0123 and 0456 as separate rows), but without relying on unsupported functions.

[wdx223_Daniel]

= Table.FromRecords(List.TransformMany(Table.ToRecords(Source),each let f=(x,y,z)=>Splitter.SplitTextByCharacterTransition(x,y)(z) in List.Select(List.TransformMany(f(each not List.Contains({"0".."9","a".."z","A".."Z"},_),each _="0",[Description]),each f({"0".."9"},each not List.Contains({"0".."9","a".."z","A".."Z"},_),_),(x,y)=>y),each Text.StartsWith(_,"0")),(x,y)=>x&[ExtractText=y]))

[telesforo1969]

Thanks.  I use Excel 2016

Expression.Error: No se reconoce el nombre 'Splitter.SplitTextByCharacterTransition'. Asegúrese de que se ha escrito correctamente.

[ronrsnfld · Accepted solution]

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.

[telesforo1969]

I'm doing something simple wrong, I just compiled your code and expanded the table.

[ronrsnfld]

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?

[telesforo1969]

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"

[ronrsnfld · Accepted solution]

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]

Thank you very much. I'm just getting started with Power Query.

[jgeddes]

Try this method...
Add a column that splits the Description by space. (NOTE:#"Changed Type" is the previous step in the query. It may be different in your query.)

= Table.AddColumn(#"Changed Type", "Value", each Text.Split([Description], " "), type list)

 Select the rows in the list that start with 0

= Table.TransformColumns(add_word_list, {{"Value", each List.Select(_, each Text.StartsWith(_, "0")), type list}})

Clean the remaining words in the lists. (Removes commas etc.)

= Table.TransformColumns(keep_0_words, {{"Value", each List.Transform(_, each Text.Select(_, List.Combine({{"-"}, {"A".."Z"}, {"a".."z"}, {"0".."9"}}))), type list}})

Expand the lists to rows...

= Table.ExpandListColumn(clean_0_words, "Value")

Complete sample code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBxdXbWNTEwUIrViVYqSa0oUTAwC/B11DU0NAUL5ZdkpBYpQCSMfF3cQRIKxfm5qWAxJF3mrj4BukbGBjoKBhYhwe66lgYKlUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    add_word_list = Table.AddColumn(#"Changed Type", "Value", each Text.Split([Description], " "), type list),
    keep_0_words = Table.TransformColumns(add_word_list, {{"Value", each List.Select(_, each Text.StartsWith(_, "0")), type list}}),
    clean_0_words = Table.TransformColumns(keep_0_words, {{"Value", each List.Transform(_, each Text.Select(_, List.Combine({{"-"}, {"A".."Z"}, {"a".."z"}, {"0".."9"}}))), type list}}),
    expand_to_rows = Table.ExpandListColumn(clean_0_words, "Value")
in
    expand_to_rows

Hope this helps.

[telesforo1969]

Thank you very much. I'll implement it and let you know.

[telesforo1969]

It repeats rows based on the words found (5), what could I be doing wrong?