The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |