Multiple value per column best way to visualise

[HMAnnex]

Hi Guys, 

So I am having a hard time figuring out whats the best way to showcase a report that I pull using microsoft forms. 

The data contains multiple value per column depending on the entry and we would need to show whats the most and least chosen option, look for the gaps and sort that issue.

This is what it looks in forms, and we wanted to link this report to powerbi with drill through of a summary per 'person/entry'

At the moment, the form data has this as columns, delimted with a semicolon. 

Name	Question 1	Question 2	Rating
Dave	apple;orange;pineapple;grapes	cinema;book;trams	4
Jon	orange;pineapple	book;trams	3
Mark	apple;grapes	cinema	1

I tried delimiting the report via column and rows this columns i end up with millions of rows as the options per question can vary from 4 options to 12 and there are about 35 questions all together. It also doesnt allign the options chosen properly as the first entry is the first option chosen. so apple might usually be the first entry but its not always the case.

What I am looking to get to is a report where a graph(maybe small multiple)/table of all the question represented. 

Is there an efficient way on moving this to powerbi without breaking my computer? 😄 

Thank you so much!

[Anonymous · Accepted solution]

Hi @HMAnnex ,

The Table data is shown below:

Please follow these steps:
1. Use Power Query to aggregate each question into a separate table

2. Split columns into rows by separator, For more information, please refer to the following article:

Split columns by delimiter - Power Query | Microsoft Learn

3. Use the following DAX expression to create a table named ‘Question1’

Question 1 = SUMMARIZE('Table','Table'[Question 1],"count",COUNT('Table'[Question 1]))

4. Use the following DAX expression to create a measure

Top choices for question 1 = 
VAR _a = MAX('Question 1'[count])
VAR _b = CONCATENATEX(FILTER('Question 1','Question 1'[count] = _a),'Question 1'[Question 1],",")
RETURN _b

5. Use the following DAX expression to create a measure

Minimum choices for question 1 = 
VAR _a = MIN('Question 1'[count])
VAR _b = CONCATENATEX(FILTER('Question 1','Question 1'[count] = _a),'Question 1'[Question 1],",")
RETURN _b

6. Use the following DAX expression to create a column

Rank = RANKX('Question 1','Question 1'[count],,DESC,Dense)

7. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HMAnnex]

Thank you so much! It'll be hefty but it does work.