Share
Have you ever tried to find a function in Power Automate that sorts arrays? If you did, you most likely know that it isn’t there.
At this point there is nothing like sort(array), or sort(array, ascending) in Power Automate. And this can be quite problematic.
Someone might point out that this is not really a problem because you can sort the array somewhere else, like in PowerApps and then pass it into the Flow or data base, already sorted.
But what if the data is already stored in unsorted way in the data base, especially saved in form of repeating section?
Let’s talk about a real-life example.
In Developico one of our clients is a big NGO. This NGO has received a grant from the government for a certain program. In this program there were around 1000 participants, whose certain skills were measured at the start of the program. After that, they took some courses and other activities that were aimed at improvement of those skills. At the end of the program, those participants had to do some tests, to measure levels of those skills again. After that, the NGO and government was able to see if the program was successful at improving of those skills.
Scores of those test were kept in SharePoint list in repeating section.
The problem started when the government required to have those skills sorted from the biggest scores to the lowest, then printed in a document.
We already had the data stored in a data base, in a repeating section, and we needed to make a flow that will sort the outcomes of the test, and then make a pdf file for each member of the program as the documentation for the government.
In that position, we could either make an app that would get stored data, sort it, pass it to the Flow and then make an pdf out of it, or somehow do it all in the flow by creating our own sort within the Flow.
We decided to go with the latter.
Let’s start with a generic example how to do it.
We have to create a flow, that has two arrays initialized.
In one step we are initializing Unsorted Array with some random numbers, and in another one, Sorted Array, which at this point is empty. In Sorted Array we will be storing sorted numbers.
Next, we have to make Apply to each loop, and as an output, select values from Unsorted Array.
First step in Apply to each loop is Filter array. As a source we have to select Sorted Array. Then we have to specify the filter. Item() must be less or equal to items(‘Apply_to_each’). This will filter out all the values in Sorted Array (which is at this point empty, but we will place values here in later steps), which are equal or lesser than current item in Unsorted Array.
As a next step we have to place another Filter array. It is the same as one before, with the difference, that we have to put Item() is greater than items(‘Apply_to_each’). This step will filter out all the items that are greater than current iteration of Unsorted Array.
Last step that is within the loop is Set variable, that will update the Sorted Array.
Here as value, there is an expression: union(body(‘Filter_array_less_than’),createArray(items(‘Apply_to_each’)), body(‘Filter_array_greater_than’))
This step will set the value of Sorted Array that is union of values in Sorted Array that are less than or equal than current iteration of the loop, current iteration of the loop that is made into Array createArray(items(‘Apply_to_each’))) and values in Sorted Array that greater than or equal than current iteration of the loop.
Let’s see couple of steps of this process to illustrate what is going on here.
First number of our Unsorted array is 2. First iteration of the loop will look like this:
- Filter array less than, or equal to 2. As this step takes items from Sorted Array, and at this point Sorted array is empty, this step will return empty array. Because in empty array there are no items lesser or equal to 2.
- Filter array greater than 2. Exactly as in previous step, this step will return empty array. Because in empty array there are no items greater than 2.
- In last step of this iteration Sorted Array will be set to union of three arrays. Filter array less than which is empty, array created out of current iteration of the loop [2], and another empty array which is Filter array greater than.
So Sorted Array has now one value in it, and it is [2].
Let’s see another iteration of the loop. Next number in Unsorted Array is 6.
- Filter array less than, or equal to 6. In Sorted Array there is only number 2, so this step will return [2], because 2 is smaller than 6.
- Filter array greater than 6. In Sorted Array there is only number 2, so this step will return an empty array, because 2 is smaller than 6.
- In last step of this iteration Sorted Array will be set to union of three arrays. Filter array less than which is [2], array created out of current iteration of the loop [6], and empty array which is Filter array greater than.
So Sorted Array contains now two values – [2, 6].
Let’s go now with the third iteration of the loop. Next number in Unsorted Array is 4.
- Filter array less than, or equal to 4. In Sorted Array there are [2, 6], so this step will return [2], because only 2 is smaller than 4.
- Filter array greater than 4. In Sorted Array there are [2, 6], so this step will return [6], because 6 is greater than 4.
- In last step of this iteration Sorted Array will be set to union of three arrays. Filter array less than which is [2], array created out of current iteration of the loop [4], and [6] which is Filter array greater than.
After 3 steps of iteration Sorted Array now looks like this – [2, 4 , 6].
This process goes for all the values in Unsorted Array. At its end Sorted Array includes all the values from Unsorted Array, but now sorted in ascending order.