Import Microsoft Forms into PowerBI Model -Multiple Choice-

[de_ppd]

Hi everyone, I'm looking for a solution to correctly display a multiple choice question from Microsoft Forms.

I have created a small example that visually represents my problem.

I have already searched for solutions in existing questions, but the links of the files are always expired.

Thanks for your feedback on the correct data modelling

[amitchandak]

You may have to split your answer

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[de_ppd]

Yes, I have tried this, but since there are questions with 7 combinable answers, the query should be flexible. Since the order is different and not all mathematically possible combinations have been taken so far. My solution was to create 7 columns for each answer and with a text search to check if the answer is contained in the whole field. This also works, but I find it a bit cumbersome.

[HotChilli · Accepted solution]

I think you should Split the column (using , as the separator).  Use the Advanced Options and Split into Rows.

You might need to clean up the column using Trim or Clean to get rid of leading or trailing spaces.

That should give you columnar data which powerbi can handle well.  The answer data from the other questions will be repeated so you need to know how to handle that correctly, for example, you should create a measure for DISTINCTCOUNT of ID and use that in visualisations