Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
How to extract out of “Column Description” the alphanumeric ID that always starts with the letter P and is followed by 7 digits and put it in a separate column?
Column Description |
Charles has engaged on Project P5671223 with Peter |
with P8702345 the delivery succeeds to saint Paul and Mary |
together with others P9906123 was a success |
William and John knew about P8765439 |
Paul and Peter as well as Anne like the breeze P9908765 brings |
Solved! Go to Solution.
I couldn't do it with DAX, but if M code is okay then
= Text.Middle(Text.Select([Column Description], {"0".."9"}), Text.PositionOf(Text.Select([Column Description], {"P", "0".."9"}), "P")
New Column =
VAR _Description = Table[Column Description]
VAR _Pattern = "P\d{7}"
VAR _Match = REGEXMATCH(Table[Column Description], "P\d{7}")
VAR _ExtractedID = IF(_Match, REGEXEXTRACT(_Description, _Pattern), BLANK())
RETURN _ExtractedID
Thx for this proposal - took me a second to find out that this is R 🙂
I couldn't do it with DAX, but if M code is okay then
= Text.Middle(Text.Select([Column Description], {"0".."9"}), Text.PositionOf(Text.Select([Column Description], {"P", "0".."9"}), "P")
nice - quick & easy done.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |