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
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.