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 😂
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
123 | |
62 | |
56 | |
47 | |
42 |
User | Count |
---|---|
113 | |
65 | |
61 | |
54 | |
45 |