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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
HILGARTH
Frequent Visitor

Extracting an variable alphanumeric ID out of a text

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

1 ACCEPTED SOLUTION
Etero
Frequent Visitor

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")

View solution in original post

4 REPLIES 4
Jayaselvan
Helper II
Helper II

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 🙂

Etero
Frequent Visitor

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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