Marcel Lehmann (mvp)

Basically, there are already some posts and videos on this topic. However, I would like to point out a great post incl. app for generating the function. (by Brian Dang)

You can find the complete tutorial under this link:
https://powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/

Below you will find a shortened version.
As already written, Brian Dang has not only provided a detailed tutorial for us in the linked post but also a downloadable PowerApp to generate the code.

Download the App: https://aka.ms/splitarray

UPDATE 11.2022: Since a while there is also a direct solution to Parse JSON in the PowerApps. The main thing is, that this solution is an experimental feature and could have massive bugs. (Link)

Preparation in the flow (PowerAutomate)

Parsing the Array in PowerApps

Following text copied from: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/

Open an existing app or create a blank one to test out your flow.

As mentioned before, you can download an app for generating a formula that can split the text string from Flow back into an array.

• Open another instance of the Power Apps Studio.
• Click File, then Open.
• Browse for the msapp file that you downloaded.

When the app opens, fill out the text boxes with details for your app:

• Name of your flow: include single quotes if your flow’s name would require them.
• Name of text field returned from flow: the name of the field you chose in the last step of your flow.
• Name of variable for flow response: the name of a variable that will be receiving the string from the flow.
• Name of collection for records: the name of the collection that will be the array you use throughout the app
• Delimiter from join: the delimiter you chose for the Join step in your flow.

Click ‘+ New column’ to add the column names in your app. Use the drop down menu beside each column to determine its type. This tool is limited to only strings, numbers, and Boolean values.