Forum Discussion

projman's avatar
projman
Frequent Visitor
5 years ago
Solved

How to count unique strings across multiple columns

Hi

I am hoping someone can help me with a table i am struggling with.

 

I have multiple columns with varying numbers of rows in each.  Across the rows/columns are various single word strings, and i am just looking to output a single column that identifies each string found and how many times it occurs.  A particular string may appear multiple times in different columns (i.e. the string 'mystring' may occur 10 times in column A and also 15 times in column C)

 

Example input:

 

ShoehatgloveSockSock
hatjacketjacketgloveglove
hatjacketshoe hat
jacketjacketSweater  

 

output i am looking for:

Shoe2
Hat4
Jacket5
Glove3
Sweater1
Sock2

 

Note that my actual dataset is huge, and i wont know all the words that are in there to manually query each word.

Any help is gratefully received

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    projman OK, I mocked this up. PBIX is attached under signature. I think I left out a DISTINCT in the table calc so that is there now.

    Table2 = 
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4])
VAR tmpCol5 = SELECTCOLUMNS(Data,"Column",[Column5])
VAR tmpTable = DISTINCT(UNION(tmpCol1,tmpCol2,tmpCol3,tmpCol4,tmpCol5))
RETURN tmpTable

    You want Data and Table2 tables in the PBIX.