Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm new to Power BI and I can't figure out how to count the number of occurence of each answer from a multiple choice sharepoint form :
I have :
Project ¦ Choice
Project1 ¦
Project2 ¦ Choice1
Project3 ¦ Choice1, Choice2
Project4 ¦ Choice3, Choice4
Project5 ¦ Choice1, Choice2, Choice3
...
And I would like to obtain
Choice ¦ number of occurence
Choice1 ¦ 3
Choice2 ¦ 2
Choice3 ¦ 2
Choice4 ¦ 1
....
The list of choices is not defined by a separate sharepoint list. I have searched through the forum and have found several subject discussing similar topics but still haven't found a way to implement this. Any help?
Solved! Go to Solution.
Hi @shugs,
If you data is in that format you present first of all you need to change you information format.
Go to the query editor and do the following steps:
Now just add you Value to the table visual and the Project (this should be count)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI have similar problem. But some columns (Response to Survey) are subjective response and the sentences may actually have a comma in them. If I use the comma delimiter solution, this will be a problem. Any suggestions?
Thanks for all the answers!
Undoutbtedly you have other columns in that table - so duplicate the table first in the Query Editor
Here's a link to a similar question
http://community.powerbi.com/t5/Desktop/Data-modeling-question/m-p/135326#M57957
Plus the image
Alternative solution in Power Query:
Resulting code (in which I adjusted the step names for better readability):
let Source = InputTable, RemovedEmpties = Table.SelectRows(Source, each ([Choice] <> null)), Splitted = Table.TransformColumns(RemovedEmpties,{{"Choice", each Text.Split(_, ",")}}), RemovedProject = Table.RemoveColumns(Splitted,{"Project"}), ExpandedChoice = Table.ExpandListColumn(RemovedProject, "Choice"), Trimmed = Table.TransformColumns(ExpandedChoice,{},Text.Trim), GroupedWithCount = Table.Group(Trimmed, {"Choice"}, {{"Number of occurrence", each Table.RowCount(_), type number}}) in GroupedWithCount
Hi @shugs,
If you data is in that format you present first of all you need to change you information format.
Go to the query editor and do the following steps:
Now just add you Value to the table visual and the Project (this should be count)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
This worked for me, thanks a lot!
But just to add - I worked with a data including more columns than the Project and Choice columns, and the Unpivot Columns solution removes the records with empty Choice column. So it's good to create a new table containing only the Project and Choice columns to avoid losing data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |