Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
shugs
Regular Visitor

Multiple choices answer count

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? 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

 

  1. Choose column Choice
  2. Transform -> Split column -> By Delimeter
    1. Choose the comma as your delimeter
    2. Now you have a table with Project and one column per response
  3. Choose all the columns besides the project
  4. Transform -> Unpivot Columns
    1. Return a table with: Project, Attribute, Value
  5. Delete column Attribute
  6. Rename Value to your choice
  7. Save and return to PBI desktop

 

Now just add you Value to the table visual and the Project (this should be count)

 

Regards,

 

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
yatinpurohit
Helper I
Helper I

I 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?

shugs
Regular Visitor

Thanks for all the answers! 

Sean
Community Champion
Community Champion

@shugs

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

Query Editor - Slicer Table.gif

MarcelBeug
Community Champion
Community Champion

Alternative solution in Power Query:

  • Remove empty Choices
  • Choose tab Transform - Format - Trim
    Adjust the generated code to use Text.Split (on ","), which will result in nested lists
  • Remove Project column
  • Expand the "Choice" column
  • Choose tab Transform - Group By Choice and count rows (which is the default operation)
    In the popup screen of "Group By", name the new column "Number of occurrence".

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

 

Specializing in Power Query Formula Language (M)
MFelix
Super User
Super User

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:

 

  1. Choose column Choice
  2. Transform -> Split column -> By Delimeter
    1. Choose the comma as your delimeter
    2. Now you have a table with Project and one column per response
  3. Choose all the columns besides the project
  4. Transform -> Unpivot Columns
    1. Return a table with: Project, Attribute, Value
  5. Delete column Attribute
  6. Rename Value to your choice
  7. Save and return to PBI desktop

 

Now just add you Value to the table visual and the Project (this should be count)

 

Regards,

 

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



iva_b
Frequent Visitor

Hi,

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

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.