Turn multiple choices in Microsoft Forms into multiple rows in Excel

[abee1]

content removed

[wskinnermctc · Accepted solution]

This should be relatively simple depending on your experience. All you need to do is put the form response with the multiple responses into an Array. Then you will use an Apply to Each on each item in the array (options selected). For each one of the items (options selected) add a row into a table.

To put the responses into an array use the json() function around the form response.

Overview of the Flow

Use the Json() function on the Form Response dynamic content to put into the ArrayCreate an Apply to Each and put the Array Variable outputs as the source - then select the Current Item dynamic content in the Excel field

Here is an example form I made and the outputs when I ran the flow.

Form ExampleExcel Table Results

The screenshots I added should help you get the flow built. Let me know if this works for you.

[abee1]

Hey! Thank you so much for this. I almost have it! I am stuck at the "Apply to Each" part since no action or operation is showing up in it. I think my "Get Response Details" might be incorrect too because I am unsure what "Response Id" to select. What are your thoughts?

[wskinnermctc]

There should only be 1 Response Id to select and it comes from the trigger.

I don't understand what part you are stuck. What do you mean no action or operation is showing up? You will have to show a screenshot.

[abee1]

This is your screenshot that I am trying to do: 

Here is the screenshot of my Apply to each. I just don't see "Current Item" available in dynamic content 

A further scrolled view

searching for it 

[wskinnermctc]

Do me a favor and switch over to the Classic Designer View instead of your current view which is Co-Pilot.

My screenshots are from Classic Designer view and it would be easier to follow if they are the same.

To get into the Classic View - click the three dots ... over by the Test button and select Switch to classic designer.

Once you get into classic designer, see if you still have issues finding things.

[abee1]

That's way better and all corrected! Thank you!!

[wskinnermctc]

Glad it works for you @abee1 ,

Can you scroll up to the post I made that has the instruction pictures and click "Accept as Solution" that way this post will be closed out and it will float the Solution to the top of the post.

[qphalen]

Hi @wskinnermctc - thanks for this solution as you have also helped me solve my problem! But I am wondering if it is possible to extend this further. I have a form where I would like to be able to select multiple names and multiples of another question which is Products as this is for a training request.

Essentially a manager would submit multiple names to be trained on multiple products and I would like these to come through as individual lines.  

In OPs post - what if there were 2+ names to choose (Tom Hanks and Stevie Wonder) and they were all to be joining BCY, ATL and NCC. That way I wouldn't have to submit a new form for each individual person. 

Can I use the same process to isolate the names as well as the LERT's? That way each has their own row in excel. 

[wskinnermctc]

Hi @qphalen , if I'm understanding your question correctly, the process would be relatively the same except you would need an additional array in the beginning for the names, then apply to each the names array similar steps from the example.

However, I don't understand how the multiple names are going to be identified? They are selecting the multiple names from the form (why would names be available for selection)? Or will they enter the multiple names via text? 

I think if they are entering the names via text then you will have an issue with errors trying to splice out individual names. Then you will start running into problems dealing with actions on single names vs multiple names.

I think they should just submit a single form per-person. It is not an unreasonable request functionally. A form should take no more than 30 seconds or a minute to complete, so that is like 2 minutes for 2 employees at most.

Meanwhile you will spend hours trying to construct a flow that can be flexible and prevent errors, just so they can input multiple people at once. The cost benefit will be to have a single form submission per person. 

If you need more assistance, you should submit a new post to the forum. Solvers/SuperUsers don't really look at older posts that were solved, so you would get an answer with a new post that is unsolved.

[SunnyRKFOODLAND]

How can I achieve this in Sharelist

[Nicole2]

I want to ask about how to form the JSON part! Thanks

[SunnyRKFOODLAND]

action before the Json you need run then to flow and then copy the output and then in then add parse json action and then click generate json from example and paste copied output in that and done. you will get json

[cjatseaspan]

Hi @wskinnermctc,

I have a similar question but its not exactly multiple choice. I have a question that has 5 options.

When I select 1 it asks details for 1 trailer

When I select 2 it asks me information of 2 trailers, then

and so on.
Right now on one submit every trailer information gets saved in one record. There's also branching involved so there can be another reservation for more trailers.

What I need is that if there were 2 trailers selected, then the information captured in those 2 text boxes should be captured in 2 different rows and not one. Is that possible through Power Automate?

Let me know in case you have further questions

Regards

CJ 

[SunnyRKFOODLAND]

Does your Microsoft Form have a section for Each Selection & each Section must have 1,1 2, 123 till 6th option for entering.

Am I Right?

[SunnyRKFOODLAND]

Below is the Test Form 

I am adding data To Excel table

and now the FLow

Add all Trailer Numbers in Compose Action 

in next action use filter array  and use below formula 

From

split(outputs('Compose'),',')

trim(item()) not equal to (blank)

and then use initialize variable and use the output of filter array in value

Hope This Helps your problem 

If I helped you please accept it as a solution and hit like.

Thanks

[cjatseaspan]

Hi @SunnyRKFOODLAND. No, there are no sections. I have 87 continuous questions and based on a selection it branches to a certain point and I have end of form at multiple questions in between.