LEFT + SEARCH Function Help

[Thomas]

Hello. I am very new to Power Query.

I have a formula I use in a normal excel worksheet:

=LEFT((MID(A1,SEARCH("( ",A1)+3,200)), (SEARCH(", S",(MID(A1,SEARCH(" (",A1)+3,200)))-1))

It takes the following string in cell A1:

DV1511H, Episode Name ( Show Name, SeriesNumber)

...and displays "Show Name" i.e. I'm trying to extract the Show Name from the above string

DV1511H, Episode Name ( Show Name, SeriesNumber)	=LEFT((MID(A1,SEARCH(" ( ",A1)+3,200)), (SEARCH(", S",(MID(A1,SEARCH(" ( ",A1)+3,200)))-1))
DV1511H, Episode Name ( Show Name, SeriesNumber)	Show Name

Is there a way to create a similar formula in Power Query ?

Many thanks

Thomas

 

[Ken Puls]

Try this one as the formula for a custom column:

Text.BetweenDelimiters([Column1], " ", ",", 3, 0)

 

[Thomas]

Many thanks, Ken. I'll try that when I get home.
By the way, I'm really enjoying your book M Is For Data Monkey. I'm still near the beginning of the book and am looking forward to becoming more proficient.

Thanks again
Thomas

 

[Ken Puls]

Cool stuff, and I hope you find it useful!

Just an FYI, you won't find this function in there - although you will find a sections that shows how to use the MID and SEARCH equivalents.

I'm actually (right now) creating a blog post to show how I got this specific formula though.

 

[MarcelBeug]

My suggestion would be:

Text.BetweenDelimiters([Column1], " ( ", ", S")

Instead of using the new functionality of adding a column by example, you can use the Add Column - Extract - Range option with some dummy starting index and number of characters, next adjust the code to the code above. This is a more direct translation of the original Excel formula.

As a matter of fact, I created a 14 minute video about the 3 new functions that were added in the April 2017 update of Power BI Desktop: Text.AfterDelimiter, Text.BeforeDelimiter, Text.BetweenDelimiters.

@Ken Puls: looking forward meeting you next week Wednesday, April 19!

 

[Ken Puls]

Awesome, looking forward to meeting you as well!