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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors