cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T_A_M
Helper I
Helper I

Can each response to a Form question populate its own row in Excel?

I have a Form with 40-50 questions, and I need to capture the responses in an Excel table. However, each answer in the form needs to populate its own row, rather than each answer populating its own column. I can do this by creating a separate "add row into a table" operation for every single question in my Form, but this gets very time-consuming and unwieldy. Is there a better way to achieve this?

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User II
Super User II

Hello @T_A_M ,

I'd store all the questions in a 'Compose' action separated by ; , that would create a long string with all the values. The use another 'Compose' action with split(...) expression to turn that string into an array using ; as the separator. That will create an array where each item is 1 response.

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

Then you can loop through that array within 'Apply to each' and create a row using the 'Current item' (each of the responses).

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

2 REPLIES 2
tom_riha
Super User II
Super User II

Hello @T_A_M ,

I'd store all the questions in a 'Compose' action separated by ; , that would create a long string with all the values. The use another 'Compose' action with split(...) expression to turn that string into an array using ; as the separator. That will create an array where each item is 1 response.

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

Then you can loop through that array within 'Apply to each' and create a row using the 'Current item' (each of the responses).

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Your solution worked great, but my situation has gotten a little more complex. Now I need to have some answers create new rows in the Excel document, and other answers create columns. It would look something like this:


In the form I would have the following questions:
Question 1
Question 1a
Question 2
Question 2a
Question 3
Question 3a

Then the answers would have to populate my Excel spreadsheet like this:

| Answer to question 1 | Answer to question 1a |
| Answer to question 2 | Answer to question 2a |
| Answer to question 3 | Answer to question 3a |

Is this something that might be possible? I've tried creating a second array with the 1a, 2a, and 3a answers, then updating my existing Excel row, but I haven't been successful. I'd appreciate it if you have any suggestions. Thanks! 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,254)