Building Flows Question: Split Multiple Choice Responses into separate columns from MS Forms to MS Excel

[Ivan_W]

I have a flow built to get information from MS Forms to MS online Excel including single choice and multiple choices questions. 

I have read several threads about how to build flows in such cases from Forms using a switch control

• https://powerusers.microsoft.com/t5/Building-Flows/Break-out-Multi-Answer-response-into-separate-cel...
• https://powerusers.microsoft.com/t5/Building-Flows/Case-Switch-from-an-MS-form-not-working/m-p/44272...

They are extremely helpful. However, when I tried myself in my case, it did not work. Following instruction of "Initialized Variable" and "Parse JSON", then I used "Apply to Each" with "Append to string variable" and "Switch". In my case, its not working on the flow design. I wish to have the flow built up has: 

1. each answer of a multiple choice question is in a separate column (e.g. multiple Q-1 has answer A and B, Excel Column E and F contain answers of the A and B respectively, not in one excel cell containing both answer A and B), and
2. the flow could get information from the Forms for both single choice and multiple choices questions (e.g. single choice question will be in Column D, and then Column E and F will be the multiple choices question)

Would appreciate if someone could help me on the abovementioned design and correct my mistakes. 

I am still new to the Power BI, many thanks for your help in advance! 

Best

Ivan

Initialize variable and Parse JSON

Switch and Case

[v-alzhan-msft]

Hi @Ivan_W ,

For your question 1: each answer of a multiple choice question is in a separate column (e.g. multiple Q-1 has answer A and B, Excel Column E and F contain answers of the A and B respectively, not in one excel cell containing both answer A and B)

You need to check how many choices has been made, such as if 3 choices is made, you need to create the new row in the excel table, and add the first choice in the Q1-Answer A column of the row, then for the second choice, you need to update the Q1-Answer B column for the row, For the third choice, you need to update the Q1-Answer C column for the row.

For your question 2:the flow could get information from the Forms for both single choice and multiple choices questions (e.g. single choice question will be in Column D, and then Column E and F will be the multiple choices question).

I suggest that you need to know all the question in the forum and create the excel table with the questions/answers, please don't related flow to identify if it is a single choice column or a multiple choice column since it would be very complex and the requirement wouldn't easy to achieve.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ivan_W]

Dear Alice @v-alzhan-msft , 

Thank you very much for the swift response and suggestion. 

I have conducted the steps as you described but there are several problems which need your further support. 

To test this, I run a demo survey consist of single and multiple questions:

1. single choice question
2. multiple choices: option 2.1, 2.2, 2.3, 2.4 and other

Then I prepared an excel consistent with the options of the survey, including all the options as indicated from your response. 

After all these being prepared, I started my flow design as the following: 

Starting here, I have two questions that need to seek your further assistance:

A. once I performed a test for the multiple question (I answered multiple question with the choices: "3" for the "other" option and chose "Option 2.3"). Performing the test in the flow, it did not go through "Condition" - "If Yes", but always went through "If No" scenario. Hope you could help me to double check the problems of this flow please. 

B. I tried to design this flow (excel automate) for one response entry per row, i.e. include each choice from the multiple choices within one row as shown in the excel design screenshot. But failed to perform this, and it seems a bit complicated... I would appreciate if you could show me how to build up the flow to have each choice per column in one row. I searched related topics here but could not find any similar answers. 

Thank you again for your consideration and support!

Best

Ivan