Forum Discussion

SteveMForm's avatar
SteveMForm
Icon for Helper III rankHelper III
2 years ago

Counting comma separated values in a column

Hi Folks   I'm using a doughnut visual to try and show a count of occurances of a each unique value in a column:     As per above, they are also comma separated, and a value can only...
DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

Hi SteveMForm ,

In order to perform such data cleansing task, Power Query is your best tool in your tool box.  I suppose there are many ways to solve your problem, but what I would do in your case is to first split that column by comma delimiter to separate columns using power Query, and then unpivot the other columns which are split into different columns, so that you have a long narrow fact table which will be easily digested by power pivot engine (dax) for futher analysis.  

Best regards,

SteveMForm's avatar
SteveMForm
Icon for Helper III rankHelper III
2 years ago

Hi DataNinja777 sorry for delay.

I have split the original column which had comma separated values in and get 4 new columns:



I now need to count the occurances of each value across the table- what is the best way to do this?

Thanks