Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to Solution.
@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 )
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
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 )
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 )
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
39 | |
20 | |
12 |