Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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