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.
OK, these pretty much do what they say. They convert text to a table. There is a version for converting a single word into a table of letters (one letter per row) and one to convert multiple words into a table of words (one word per row). Could be useful...
The code for the table conversion is the __Table variable in each of the formulas. The rest of the code is simply some extra stuff to do something interesting with that table.
Text2Table1 Measure =
VAR __SearchText = MAX('Table'[SearchText])
VAR __Vowels = { "a", "e", "i", "o", "u" }
VAR __Table = SELECTCOLUMNS(ADDCOLUMNS(GENERATESERIES(1,LEN(__SearchText),1),"Letter",MID(__SearchText,[Value],1)),"Letter",[Letter])
VAR __Intersect = INTERSECT(__Table,__Vowels)
VAR __CountDistinct = COUNTROWS(DISTINCT(__Intersect))
RETURN
IF(ISBLANK(__CountDistinct),0,__CountDistinct)
Text2Table2 Measure =
VAR __Find = { "when", "given", "then" }
VAR __SearchText = MAX('Table'[SearchText2])
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText," ","")) + 1
VAR __Table =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),"Word",
VAR __Text = SUBSTITUTE(__SearchText," ","|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text) + 1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(" ",__Text,__Start)
)
VAR __Word = MID(__Text,__Start,__End - __Start+1)
RETURN __Word
),
"Words",[Word]
)
VAR __Intersect = INTERSECT(__Table,__Find)
VAR __CountDistinct = COUNTROWS(DISTINCT(__Intersect))
RETURN
IF(ISBLANK(__CountDistinct),0,__CountDistinct)
eyJrIjoiNWIxMjBjYzAtNjIwZC00NzUwLWI0NzEtMWZhNjA2MmFhMWIzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
I revisited this solution after I remembered my Mythical DAX Index. A perhaps more elegant approach to Text to Table if you don't have | characters in your text is this:
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|")
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",PATHITEM(__Text,[Value],TEXT)
)
If you did have | characters in your text, you would have to SUBSTITUTE them out and then SUBSTITUTE them back in for each word which could get a little messy.
Hi @Greg_Deckler ,
Great post I used a similar approach to answer a problem on the forum, altough it was created as a column (by request of the user) the approach for the measuse is just a small adjustment on the first line using SELECTEDVALUE.
You are always adding more and more value 👍👍
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português