Forum Discussion

ViniciusLeyro's avatar
ViniciusLeyro
Frequent Visitor
3 years ago
Solved

Count different items from different columns

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.

 

NameAvailable onInterestVehicle
JohnMonday, Friday, SundayFootball, Skiing, CyclingCar, Motorcycle, Bicycle
PaulTuesday, Friday, Saturday, SundayFootball, Basketball, GolfCar, Bicycle
AndrewSaturday, SundayGolf, SkiingMotorcycle
SteveTuesday, SundayBasketball, CyclingCar, 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 😂

Need Help
Tutorial Requests
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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.

     

     

1 Reply

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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.