Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
65 | |
63 | |
56 | |
39 | |
27 |
User | Count |
---|---|
85 | |
58 | |
45 | |
43 | |
38 |