cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shanks_403
Frequent Visitor

Selecting text between a list of end delimiters

Hello,

 

I'm fairly new to PowerQuery and looking for some help. I'm trying to extract a text where the end delimiter varies. The Extract function only lets me select one type of end delimiter. I'm trying to get everything after the WR# and before the "-", " ", or "-".

 

Original Table                                      Desired Outcome

WR#84658 - For Material                    84658

This WR#8457231 is for chairs            8457231

Received WR#12394. Thanks              12394

 

Any help would be greatly appreciated!

5 REPLIES 5
ronrsnfld
Resident Rockstar
Resident Rockstar

Add a custom column with the formula:

Replace Phrase with the name of the column containing your phrases

 

Text.SplitAny(
Splitter.SplitTextByDelimiter("WR#")([Phrase]){1}," -."){0}

 

ronrsnfld_0-1679794127933.png

 

 

 

 

halfglassdarkly
Resolver IV
Resolver IV

halfglassdarkly
Resolver IV
Resolver IV

As you've found, you can use (under the ribbon menu):

Transform > Extract > Text Between Delimiters.

I think you'll have to apply a couple of subsequent transform steps to extract text before your other two delimiters.

 

In your example your first delimiter would need to be " " not "-" unless you want a trailing space after the number, so you may be able to limit your end delimiters to " " and "." ?.


If you don't want this to appear as multiple transform steps you could add a custom column referencing [Outcome] and use nested instances of Text.BetweenDelimiter and Text.BeforeDelimiter to achieve the same output.


See also https://learn.microsoft.com/en-us/powerquery-m/text-functions for documentation on the M functions behind this and other text transformation options. 

 

Thanks for your reply!

 

The text between delimiters under the Transform>Extract>Text Between Delimiters only lets me pick one End delimiter. In my case, there could be several different types of end delimiters. Is there a way to use that function for this scenario?

Yes, sorry re-read your question after posting and realised that. I've amended my original reply.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors