cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ViniciusLeyro
Frequent Visitor

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 😂

1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

Hi  @ViniciusLeyro  

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

your data:

Mikelytics_1-1674486053359.png

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

Mikelytics_3-1674486216065.png

Mikelytics_4-1674486266122.png

Firwst result

Mikelytics_5-1674486306253.png

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

Mikelytics_6-1674486356579.png

 

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

Mikelytics_10-1674486649230.png

 

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

Mikelytics_11-1674486772430.png

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.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

1 REPLY 1
Mikelytics
Resident Rockstar
Resident Rockstar

Hi  @ViniciusLeyro  

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

your data:

Mikelytics_1-1674486053359.png

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

Mikelytics_3-1674486216065.png

Mikelytics_4-1674486266122.png

Firwst result

Mikelytics_5-1674486306253.png

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

Mikelytics_6-1674486356579.png

 

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

Mikelytics_10-1674486649230.png

 

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

Mikelytics_11-1674486772430.png

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.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors