Problem with extracting multiple substrings from a text field

[a_m_wood]

Hi,

I'm trying to extract any occurences of text starting with the patter "-W" in a field "Name"

I'm getting an error on record 2

Expression.Error: The 'count' argument is out of range.
Details:
6

If matches on this record is 

 and the length of "Name" is 24,is my 'try' 'otherwise' not working or wrong?

Thanks in advance.

[dufoq3 · Accepted solution]

Hi @a_m_wood, check this

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCknNLchJVQguUQhOTVcwNFDQVQg3NDExA9JBIW6G5uYmCpWpJQol+QpJqQoFRZnJqSlKsTrRSk6picn5eQpBKcj6zC0twJIhqcUlCrrhhkbGJgqJSckpIElTM3MLhdS09Ayl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    Ad_ExtractedW = Table.AddColumn(Source, "Extracted W", each
        [ a = List.Combine(List.Transform({"- W", "-W"}, (x)=> Text.PositionOf([Name], x, Occurrence.All))),
          b = List.Transform(a, (x)=> Text.BeforeDelimiter(Text.Trim(Text.Range([Name], x), {" ", "-"}), " "))
        ][b], type list)
in
    Ad_ExtractedW

[a_m_wood]

Thank you!

[dufoq3]

You're welcome 😉