topic Re: Extract specific text from string in DAX Commands and Tips

Extract specific text from string

OSSKD1 — Mon, 07 Nov 2022 17:05:07 GMT

I need to extract the 6 digit number, dash, and either 1,2, or 3 digit number following the dash from the dataset shown below. What is the easiest way to accomplish this as there isn't consistency in the original dataset.

Re: Extract specific text from string

daXtreme — Mon, 07 Nov 2022 17:13:34 GMT

You use Power Query for such data mangling...

Re: Extract specific text from string

tamerj1 — Mon, 07 Nov 2022 17:18:16 GMT

Hi OSSKD1
Please copy/paste the same sample data

Re: Extract specific text from string

OSSKD1 — Mon, 07 Nov 2022 18:24:45 GMT

Copying data similar to screenshot supplied previously:

FOR CATS RFQ REF 254066-912

263117 - 3

FOR CATS RFQ 247178-4

FOR CATS RFQ REF 249237-9

FOR CATS RFQ 255000-44

54 V&V PLANS & TESTING

FOR CATS RFQ 229218-71

FOR CATS RFQ REF 247178-18

FOR CATS RFQ 255840-187

ENG FEES (CATS HRS) RFQ: 245774-9

FOR CATS RFQ 246784-29

FOR CATS RFQ 247178-7

280352 - 1

FOR CATS RFQ 240026-26

FOR CATS RFQ 255000-42

276196 - 16

276084 - 78

276922 - 6

276196 - 139

FOR CATS RFQ 242212-14

276922 - 5

FOR CATS RFQ REF 247095-4

263051 - 86

FOR CATS RFQ 247178-5

264720 - 255

FOR CATS RFQ 255278-8

FOR CATS RFQ 254104-5

FOR CATS RFQ 247178-15

277337 - 21

270392 - 25

Re: Extract specific text from string

tamerj1 — Mon, 07 Nov 2022 19:06:38 GMT

Hi OSSKD1
Please refer to attched file with the solution

Result = VAR String = 'Table'[Text] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) ) VAR Result = CONCATENATEX ( T4, [@Item], "-" ) RETURN IF ( CONTAINSSTRING ( Result, "-" ), Result )

Re: Extract specific text from string

OSSKD1 — Mon, 07 Nov 2022 20:41:17 GMT

Thank you so much for the quick and helpful solution!

Re: Extract specific text from string

OSSKD1 — Tue, 08 Nov 2022 13:56:43 GMT

Hi tamerj1,

Quick follow up from yesterday's solution. I noticed in my master data and also in the file you provided, that in certain rows, the sequence was reversed (the 3 digits are now in front and the 6 digits are last) per the screenshot below. Any ideas what in the DAX might cause this?

Re: Extract specific text from string

tamerj1 — Tue, 08 Nov 2022 15:58:25 GMT

Hi OSSKD1
Attached file with small correction

Result = VAR String = 'Table'[Text] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) ) VAR Result = CONCATENATEX ( T4, [@Item], "-", [Value], ASC ) RETURN IF ( CONTAINSSTRING ( Result, "-" ), Result )

Re: Extract specific text from string

OSSKD1 — Wed, 09 Nov 2022 16:15:49 GMT

Thank you! That solved all my issues, very much appreciated!