Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
Super User
Super User

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
Responsive Resident
Responsive Resident

halfglassdarkly
Responsive Resident
Responsive Resident

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors