how to perform count operation for a column with multiple values in Power BI?

Question

So, let this be a dataset

Column 1(text)          |     Column 2(text)

textvalue1              |     ab,bc,cd,de,ef 
textvalue2              |     ab,bc  
textvalue3              |     cd,de,zz,ns,ab  
textvalue4              |     ab,bc,zz,de  
textvalue5              |     de  
textvalue6              |     io
textvalue7              |     de
textvalue8              |     ab,bc,cd,de,zz

Now I want to find the Top 3 column2 values(individual not grouped, I mean it could be ab, de and zz for example) on the basis of the count of values in column 1. So, as per the above data the counts of column 1 for each item in column 2 are: -

ab = 5
bc = 4
cd = 3
de = 6
zz = 3
ns = 1
io = 1

The answer shall be de,ab and bc.

Thanks in advance :)

Answer 1

Use Power Query to clean up the data.

• Split Clumn 2 into separate columns, using the comma as the delimiter
• use the Unpivot functionality in Power Query to unpivot the data, so you have a flat table with one value per row.

Now the data is normalised and you can perform standard aggregations with ease.