topic Re: Extracting an variable alphanumeric ID out of a text in DAX Commands and Tips

Extracting an variable alphanumeric ID out of a text

HILGARTH — Wed, 20 Dec 2023 16:47:46 GMT

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

Re: Extracting an variable alphanumeric ID out of a text

Etero — Wed, 20 Dec 2023 18:44:25 GMT

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

Re: Extracting an variable alphanumeric ID out of a text

Jayaselvan — Wed, 20 Dec 2023 19:25:41 GMT

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

Re: Extracting an variable alphanumeric ID out of a text

HILGARTH — Thu, 21 Dec 2023 10:53:37 GMT

Thx for this proposal - took me a second to find out that this is R 🙂

Re: Extracting an variable alphanumeric ID out of a text

HILGARTH — Thu, 21 Dec 2023 10:54:25 GMT

nice - quick & easy done.