“I’ve got a multiple selection choice question from MS Forms that I’d like to split into separate choices, how can I do it in Power Automate?”
While an array has a given format [“value1”, “value2”], it doesn’t mean that each string in such format will be considered as an array. It often depends on the source of the string. If it’s a multiple choice question in MS Forms, it’ll be a string. If it’s stored in a SharePoint column, it’ll be a string as well.
It can be a bit confusing as if you enter the value into ‘Compose’ action, it’ll show you the right array format. But this happens only because the ‘Compose’ action removes escape characters. When you ‘Show raw inputs’ you’ll see these escape characters that are otherwise ignored.
These characters are what makes the difference between an array and string formatted as an array. To convert such string to an array, you must get rid of them. And the easiest way to achieve that is the json(…) expression.
Use json(…) expression
You might be tempted to use some combination of replace(…) expressions to remove the extra characters and then split(…) it. But that’s not necessary, you can use a single expression that’ll remove them for you. It’s the json(…) expression that will take a string and convert it to valid json. And as part of the conversion it’ll remove all the escape characters.
json([stringInArrayFormat])
e.g.
json(outputs('Get_response_details')?['body/r4a2197a370d846029b78cdbf87270043'])The output of this expression will be a valid array.
You can use it as the input of ‘Apply to each’…
…or convert it into a nicer formatted string with the join(…) expression.
Summary
While it might look that Forms gave you an array, it always returns a string. And the easiest way to split the Forms multiple selection response into separate value is to use the Power Automate json(…) expression.
Unless it’s a simple string with a fixed separator where the split(…) expression will do the trick.
Thank you! This was extremely helpful!
If you have something like this [“Name a color”:”Red”,”Favourite food”:”Pancakes”] of type string. Can I convert it somehow to json array? (Basically trying to make an object into an array using your solution but running into errors when adding the json expression)
Hello Sara,
I don’t think there’s an easy way to convert such string into JSON, it would probably need multiple split(…) and replace(…) expressions to somehow hard-convert it.