Text to Table

Question

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...
&nbsp;
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)
&nbsp;
eyJrIjoiNWIxMjBjYzAtNjIwZC00NzUwLWI0NzEtMWZhNjA2MmFhMWIzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

mfelix · Answer

Hi&nbsp;Greg_Deckler&nbsp;,
&nbsp;
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.
&nbsp;
https://community.powerbi.com/t5/Desktop/Matching-and-returning-a-list-of-all-matching-words-within-a/td-p/1056040/page/2
&nbsp;
You are always adding more and more value 👍👍

greg_deckler · Answer

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.