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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bmk
Helper II
Helper II

Extract n words from string

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"

1 ACCEPTED SOLUTION

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

1.png

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 )

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

@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. 

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.

 

bmk_0-1674850247783.png

 

 

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

1.png

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 )

thank you!

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors