Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
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}
Also check out this solution as another option: https://community.powerbi.com/t5/Power-Query/PowerQuery-Text-before-multiple-Delimiters/td-p/1955908
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |