topic Re: Text to Table in Quick Measures Gallery

Text to Table

Greg_Deckler — Fri, 21 Aug 2020 01:50:37 GMT

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

Re: Text to Table

MFelix — Mon, 24 Aug 2020 10:35:08 GMT

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.

https://community.powerbi.com/t5/Desktop/Matching-and-returning-a-list-of-all-matching-words-within-a/td-p/1056040/page/2

You are always adding more and more value 👍👍

Re: Text to Table

Greg_Deckler — Mon, 30 Aug 2021 13:52:36 GMT

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.