cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bjsebeck
Frequent Visitor

Splitting results from a multiple choice Forms response into separate lines within a cell in Excel

I have a form with a multiple choice response and a flow that pulls the response data and dumps it over in an Excel table.

 

When I just do that without doing anything to the output, I get (for example) ["apple","banana"].

 

I've learned how to get that to turn into apple,banana by taking the output from that question and dumping it through 3 nested replace functions (one each for the [,], and " characters).

 

What I would like to do is to have the result in excel look like this:

apple

banana

 

Is there a way to have PowerAutomate paste that data into the Excel table with a line break there? 

 

I've already tried doing yet another replace to change the , into a <br>, but that just results in apple<br>banana.

 

I also need to apply this to multiple (but not all) questions on the form.  Is there a more elegant way to implement it other than just a whole series of compose actions, one for each question that I need to do this for?

3 REPLIES 3
DamoBird365
Super User
Super User

HI @bjsebeck 

 

This should work:

 

Create a compose action with a physical return line in it, and then join the result with the return line.  No need for a replace.

 

DamoBird365_0-1620922049493.png

 

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


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Huh.  This is interesting.

 

I stumbled upon a slightly more complex / different solution when continuing to poke at this.  I initialized a variable with a value of a return line, then did a replace and swapped the comma for the new variable that I just initialized.  It worked a treat. Interestingly, when I looked at the raw output, the variable initialized as \n, but when I changed the value that it initialized the variable as to \n, it didn't create the new line, just stuffed the characters into string.

 

When I just tried out the method that you suggested to see if there's multiple ways to solve my issue, I got an interesting response.  Repeating exactly what you have, it worked just fine.  Fed the results of your Compose 2 over to a row in an table in Excel and it does exactly what I wanted it to.  But, when I replaced the composed array with the output from a quick test form that I had been working with (my form and flow are live, so I created a little test environment where I can muck about without risking somebody filling out the form and triggering the flow while it's down) and the flow errored out, stating that the join expects an array, but the output from the form is a string.  When I first dumped the form response through split, the result was:

[
  "[\"apple\"",
  "\"bannana\"]"
]
Which I'm pretty sure implies that while they may be formatted as an array, the forms output is a string that just happens to look like an array.
 
So, it appears that the easiest way to do what I'm looking for is to first initialize a variable of a physical return line, then when I do the Add a Row to a Table action and am assigning what goes into which column, for those locations where I have the multiple choice answers, use the expression composer tool to do this:
 
replace(replace(replace(dynamic output from form,'","',ReturnLine),'["',''),'"]')
 
Which will take the forms string output of ["apple","bananas"] and, once fed over to Excel, result in apple and banana in the same cell with a physical return putting them on separate lines.
 
I have learned a lot today.

Sorry about that one @bjsebeck 

 

Good news though, if you use the expression json() on your answer, you can then implement the solution I have provided.

 

DamoBird365_0-1620931974763.png

 

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


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,605)