A while back I wrote about how to sort an array in Power Automate and I used an Azure function to do the job. One of the annoying things of that approach is that you will need to use a premium licence.
But what if you don’t have a premium licence available?
I’m aware of the user voice created by Laura Graham-Brown asking for a sort function. But that hasn’t been implemented yet.
Also John Liu’s option option for sorting can work, but in this post I’m going to reduce the number of steps needed.
My array
Table of Contents
I’ve created an array of data with number in random order.
In my example I wanted to keep my data simple. It is however possible to create more complex arrays of data and then sort the array of data using the method described this post.
Add rows of data to Excel
Now that I’ve got my array I will add rows to a table that I created in an Excel file. The table only had one column, but there is no reason why I couldn’t use multiple columns in this case.
This is going to be slow as each Add a row into a table action will take a few seconds. Just to add 9 items to an Excel table takes about 16 seconds!
When you have larger arrays this isn’t going to work very well. We will need to speed this up!
Concurrency control
But there is hope!
I set the concurrency control on the Apply to each to a maximum of 50.
Now we are down to 2 seconds! That is more like it!
I’m happy with this performance and I on top of that I noticed that my Excel file doesn’t get locked at all. I expected this to go wrong, but the Excel connector must have been improved a lot!
Wow, time is flying when we’re having fun!
Now that the data is in my Excel file all I need to do is collect the data back.
Sort an Array using Excel in Power Automate
The a Excel connector as an action called List rows present in a table. This action collects all the rows in a given table and in the advanced option there are filtering and sorting options available.
In this post I will use the Order by option to sort my data. In my table I only have one column. If you have multiple columns then you might want to Order by one or more columns.
At this stage you could even apply a filter but you might as well do that with the filter action in flow.
Cleaning up the table
If you want to reuse the excel file every time you run your flow then you migth want to clean up the table.
Remember to enable the Concurrency control setting! Otherwise your Delete a row actions will be very slow again.
Cleanup the Result
The List rows present in a table action will create an array with a bit of junk. Power Automate’s Excel connector simply ads some information that it uses to structure the data.
I’m cleaning up the data by pushing the data returned into a compose action set to the following expression:
body('List_rows_present_in_a_table')?['value']We could of course skip this Compose step, however for debugging purposes I do sometimes add some additional Compose actions so that I can very quickly get to the elements in the json that I’m interested in.
Then finally I use the Select to just select the information that I’m interested in:
item()?['Data']
This results in a nice clean array with my numbers ordered
So now we have an easy way to sort an array, using all standard features within Power Automate.
Ok that is an interesting way to do that. Will file that one and try to remember it when I need to do that.
Do you know of anyway to sort date/times? I’m trying to make a flow that’ll return the next 5 times two users will have open (using “Find Meeting Times”). But it’s returning random dates that aren’t in order.
Hi Brett, where is the data coming from? Many actions have sorting options available.
If you have dates, you could also use the ticks function to sort an array of dates
Hello Peter, thanks for the reply. I figured this one out.
The data is coming from the “Find Meeting Times” action from Outlook 365, which is put into an array. What I am trying to accomplish is allowing a user to click “More options” in Teams, fill out a quick adaptive card, then find available meeting times between that user and the sender of the message. I completed it successfully yesterday.
I figured out how to sort the array of dates/times using a SharePoint list as a workaround. Here’s the steps I took, below, just in case anyone else is looking for a way to sort arrays.
SharePoint List “Order by” workaround for sorting an array of dates in Power Automate:
> On a selected teams message, choose “…” then “More actions” then “Find Mtg Times”
> Initialize ‘MeetingTimes’ array variable
> Find Meeting Times (Outlook 365 action) with message sender
> Apply to each (output = meetingTimeSuggestions – autocreated by choosing the DateTime value for “Convert time zone”)
>> Convert time zone (converts timezone to a ‘sortable’ format, in my timezone [‘Sortable date/time pattern – 2009-06-15T13:45:30 [s]’])
>> Create item (SharePoint action, creates item in a list)
> Get items (SharePoint action, gets list items. Go to “advanced options” and Order By “Title”, Top Count = 5)
> Apply to each (Output = “value” from SharePoint Get Items action. Then Converts Timezone action to more readable format. Then appends that value to the ‘MeetingTimes’ array variable
> Teams action – Post a set of options to a user as the Flow Bot (this sends the available times to the user)
> User selects time/date
> Create a Teams Meeting action
I also have this set up to allow a user to create an invitation to an entire team, all from within Teams. Pretty cool.
Your post here had some great suggestions, but for some reason I just couldn’t get the Excel “add a row to a table” thing to work consistently. But what your post did for me was allow me to see what else I could do to solve it. The magic of Flow: several ways to solve a problem.
Appreciate you taking the time to write it. And to write back! I’m sure I’ll be hitting you up with more questions soon as I work more into the Power world!
Thanks Brett. Great feedback