topic Text.BetweenDelimiters function, bug or user error? in Power Query

Text.BetweenDelimiters function, bug or user error?

Anonymous — Wed, 23 Aug 2023 16:06:41 GMT

Greetings all,

I’m nesting the Text.BetweenDelimiters function within a Text.Select function and I’m getting a result that looks wrong. It looks like the BetweenDelimiters function is not working property; is this a bug or am I not understanding how the BetweenDelimiters works?

I’m trying to extract inbound ocean container IDs (from a SharePoint calendar if it matters). Those container IDs are surrounded by a dash at the front and an open paren after the ID. Hence, extraction between delimiters “-“, “(“.

That works great when both delimiters are present, I am indeed getting my container ID.

However, I believe that where there is no second delimiter, a null result should come back; all those “56pallets” should be null, yes?

Why is the Text.BetweenDelimiters function happy to ignore the absence of the second, enclosing delimiter?

Thanks!

The code might be hard to read in the screen snip, here is the text of the code:

= Table.AddColumn(#"Changed Type1", "Container ID", each
Text.Select
(Text.BetweenDelimiters([Event Title], "-", "(" ),
{"a".."z", "A".."Z", "0".."9"})
)

Re: Text.BetweenDelimiters function, bug or user error?

mussaenda — Thu, 24 Aug 2023 10:07:50 GMT

Hi Anonymous ,

Then nest your formula with if Text.Contains functions.

Like if the Event Title contains "-" and "(" then your nested formula else, null

You think that will work for you?

Re: Text.BetweenDelimiters function, bug or user error?

Anonymous — Thu, 24 Aug 2023 13:38:27 GMT

mussaenda

🎉😊👏🎉

= Table.AddColumn(#"Changed Type1", "Container ID", each

if Text.Contains ([Event Title], "-") and Text.Contains ([Event Title], "(") then
Text.Select
(Text.BetweenDelimiters([Event Title], "-", "(" ),
{"a".."z", "A".."Z", "0".."9"})
else null)