Survey with both value (1-5) and multiple choice text questions

[KQngen]

Hello guys,

I have a problem with creating a rapport for survey data that is a mix of text and numbers.

Above you see the survey (You can click the image to go to the real survey). In question one to three the smileys represent a number from 1-5.
In question 4-5 the user can select multiple answers.
In the database the data looks like this in the "question response" table:

We also have a "question" table that looks like this:

We wish to create a chart that shows the amount of answers for each of the multiple choices in question 4-5.

[KQngen]

@v-kkf-msft  Thank you for your thorough answer!

[v-kkf-msft · Accepted solution]

Hi @KQngen ,

First add a coustom column as a copy of column msfp_reponse in Power Query Editor.

Then select the Coustom column, select Home -> Split Column -> By Delimiter.

Split text based on comma separator. The following results are obtained. 

Then create visuals.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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