Forum Discussion

shugs's avatar
shugs
Regular Visitor
9 years ago
Solved

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       ...
  • MFelix's avatar
    MFelix
    9 years ago

    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

     

     

     

MFelix's avatar
MFelix
Super User
9 years ago

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

 

 

 

iva_b's avatar
iva_b
Frequent Visitor
5 years ago

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.