Forum Discussion

telesforo1969

Helper V

1 year ago

Solved

Extract 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...

Need Help

ronrsnfld

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.

ronrsnfld
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.

jgeddes

Super User

1 year ago

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