topic Re: Extract n words from string in DAX Commands and Tips

Extract n words from string

bmk — Fri, 27 Jan 2023 17:03:16 GMT

Hello, I wish to extract certain number of words from the beginning of a sentence based on a delimiter, space character being the most generic.

Example : "word1 word2 word3 word4 word5"

Output (in case I only need first 2 words) : "word1 word2"

Output (in case I only need first 3 words) : "word1 word2 word3"

Re: Extract n words from string

tamerj1 — Fri, 27 Jan 2023 17:20:09 GMT

bmk

Just to properly understand, when you say select 2 words or 3 words, do you mean select a number from a parameter table?

and how would you like to disply the rests? In table visual, each record in a row? Or you want to spilit each record into 2 or 3 rows based on the selection? Would you please be more specific and provide more details?

In general yes this can be done.

Re: Extract n words from string

bmk — Fri, 27 Jan 2023 20:11:03 GMT

I have a table and I wish to have a calculated column in which the first 3-5 (this is dependent on what the user selects) words of a string is selected.

No further calculations is needed. Example table is below, with first 5 words being extracted in the 'Output' column. Please let me know if this needs to be clarified further.

Re: Extract n words from string

tamerj1 — Sun, 29 Jan 2023 09:51:23 GMT

bmk
Sorry for the the late reply. Please refer to attached sample file, I hope this is what you're looking for.

Output = VAR N = MAX ( Selection[Last n Words] ) VAR String = SELECTEDVALUE ( 'Table'[Column] ) VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) VAR T3 = FILTER ( T2, [Value] <= N ) RETURN CONCATENATEX ( T3, [@Item], " ", [Value], ASC )

Re: Extract n words from string

tamerj1 — Sun, 29 Jan 2023 09:55:11 GMT

bmk
The following code returns the full text incase there is no selection or if multiple items are selected

Output = VAR String = SELECTEDVALUE ( 'Table'[Column] ) VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR N = SELECTEDVALUE ( Selection[Last n Words], Length ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) VAR T3 = FILTER ( T2, [Value] <= N ) RETURN CONCATENATEX ( T3, [@Item], " ", [Value], ASC )

Re: Extract n words from string

bmk — Tue, 31 Jan 2023 16:57:07 GMT

thank you!