Count different items from different columns

[ViniciusLeyro]

I have a table like this, it comes from a googleforms and is updated every new answer.

I don't have access to the form so they will keep coming like this.

Name	Available on	Interest	Vehicle
John	Monday, Friday, Sunday	Football, Skiing, Cycling	Car, Motorcycle, Bicycle
Paul	Tuesday, Friday, Saturday, Sunday	Football, Basketball, Golf	Car, Bicycle
Andrew	Saturday, Sunday	Golf, Skiing	Motorcycle
Steve	Tuesday, Sunday	Basketball, Cycling	Car, Motorcycle

I need one graph for each column, showing the amount of answers for each of the items.

So:

- how many people available on each day of the week

- how many people interested in each sport

- how many people own each of the vehicles

While also allowing to know for example who and how many people on sundays can use a car, etc.

I suppose it's simple, but I'm new in powerbi, stuck on that problem, and don't even know how to look for the solution 😂

[Mikelytics · Accepted solution]

Hi  @ViniciusLeyro  

You can use the split by column function to cleanse the data. Please see the following approach:

your data:

Select the first column and do ths split with advanced option = Rows

Firwst result

now do the same again with the other two columns. Then you will have a clean table with all combinations

For the first two questions you can build now a measure with DISTINCT

ADDON

in the end I have seen that I have duplicates in the visual so do cleanse please also do a trim on the columns as a last steps in power query before loading into data model

then it should be fine and not with the duplicates in the visual like above

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.