How to convert string to an array in Power Automate
Posted On July 21, 2021
“I received a multiple choice question from MS Forms that looks like an array, but Power Automate tells me it’s a string, how can I convert that string to an array?”
“The execution of template action ‘…’ failed: the result of the evaluation of ‘foreach’ expression ‘…’ is of type ‘String’. The result must be a valid array.”
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.
The output of this expression will be a valid array.
When you see the error message above, don’t believe the ‘Compose’ action which you might try to validate the value. If Power Automate tells you it’s a string, not an array, then you must convert it. And the json(…) expression is the easiest way to achieve this.
Unless it’s a simple string with a fixed separator where the split(…) expression will do the trick.