Answered

format data with expression

(0) Share

Report

Posted on by jcole5352

Hello!

In my flow I am trying to write an expression to format the way some data is being pulled in. Some background: my data is coming from a Microsoft Form and one of the questions on the form is multiple choice. My flow is dumping the form answers into Excel and for the question that is multiple choice, it is displaying the data in that field in Excel like this: ["Bitewings","Periapicals","FMX"].

I'm trying to write an expression to format the data and the "Format data by examples" tool gave me the suggested expression and it works great if the number of items in my list is 3 because that's how many I used when I asked for the expression. So, if the person chooses 3 items on the form question then my flow converts this separated nicely by semi colons as I've desired. However, if the person chooses 1, 2 or 4, 5 answers then the flow errors out.

How do I write an expression to convert the output regardless of how many items are in the list?

THANKS!

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Matthy79 4,180 Super User 2024 Season 1 on at

Like (1)

Report

Hello,

this is a simple array. Let “join” do the work.

Best regards.

Was this reply helpful? Yes No
hrangel-gg 173 on at

Like (0)

Report

Expanding a little on @Matthy79 's idea, you can use a Parse Json action to convert that to an array and use the join function. It works regardless of quantity of items.

Was this reply helpful? Yes No
jcole5352 58 on at

Like (0)

Report

@Matthy79 @hrangel-gg
Thank you both! This got me closer for sure! I am not a programmer or even close to being techy, so I know nothing about JavaScript or anything else. I'm just trying to make some better use out of a Microsoft Form that we use. 🙂
So, your solution did work and it got rid of the [] and "" junk, however, it put these records into my Excel as two different lines which will throw off my reporting when looking at the number of patient records. These are the same patient and should be all one line.

My desired output was to have that field display the items separated by a semi colon like this:

Which IS how the output is displayed in Excel if I just manually do Open in Excel right from the Response tab of the Microsoft Form itself. However, when it's dumping into an Excel via my Power Automate flow, it was encapsulating the items with quotes and brackets.

So, getting rid of the junk is closer, but any thoughts on how to get them on one line separated by a semi colon?

I will also attach a screenshot of my flow to make sure I've set that up correctly too.

TIA!

Was this reply helpful? Yes No
Verified answer

Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

As far as I can see you are looping the "Type of radiograph taken". If you do this, it will insert a row for each selected item. If you only want one row you shouldn't create an apply to each and only use "add a row into a table" (without the loop). You also don't need the parse JSON or Compose. And for the cell "Type of radiograph taken" you put in the formular "join(XXXXX, '; '). For XXXXX you put in whatever is currently the content of "Parse JSON".

Was this reply helpful? Yes No
jcole5352 58 on at

Like (0)

Report

AH! This was it!
I didn't notice the "apply to each", I think it popped that in there when I added the parse JSON or the Compose action, one of them because originally I was just using Add a new row to table. I have it back to just that now and changed the other items you said. This worked perfectly!!!

THANK YOU SO MUCH!!!!

Was this reply helpful? Yes No