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 ...
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.

projman's avatar
projman
Frequent Visitor
5 years ago

This is great, thank you!

 

Unpivoting them isn't a problem.  In fact, they were originally just a single column, but I extracted them out based on a colon delimiter.  I still have the original single column data, however, I couldn't see how to split that out be individual word, only by combinations of word.  i.e. shoe;jacket and shoe;jacket;sock, when it was in that form, which is why i split them into individual columns

 

So using your pbix I think I am halfway there!  Using mc Count Distinct I now have the total count of distinct strings, but how do I go about outputting each string to a new row and then showing its total count occurrence next to it?

 

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    projman Well, you could create a separate unrelated table like this:

    Table = 
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Value1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Value2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Value3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Value4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
RETURN tmpTable

    Then your measure would be:

    MC Count = 
VAR value = MAX('Table'[Column])
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Value1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Value2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Value3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Value4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
VAR tmpValue = COUNTROWS(FILTER(tmpTable,[Column]=value))
RETURN tmpValue

     

    As for the Power Query thing, you would split out the values based on your delimiter. You would then select your other columns, right-click and "unpivot other columns". This would then give you a column with all of your single words in a single column in theory.