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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors