Sorting array based on dates

(0) Share

Report

Posted on by rwin45

Hi,

I am using the list rows function and then a condition to build an array.

The excel sheet has a "Last Update" column indicating the last time we received an update on that job. The array will include jobs that have we did not receive an update for yesterday. The condition checks if the "Last Update" value is less than or equal to subtractFromTime(UtcNow(),48,'Hour'), then appends to array if true.

Once the array is compiled, it creates an HTML table and sends via shared email. The flow will run everyday at noon. Everything is working fine and this is going to be nit picky, but I want the array to be sorted by the 'Last Update'.

The problem is I want the date values in the table to be in the format "Month Day" (i.e. June 23). I cannot sort the array unless they are in the following format:

2019-12-06T22:47:23.0798367Z

Is there anyway I can sort the array, then change the format of the date within the sorted array before creating HTML table?

Any help is appreciated

Categories:

General topics

All responses (4)

Answers (1)

Sort by

abowen 222 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @rwin45!

I would recommend using another Apply to each loop to work through your array and create a new array with the sorted values in the correct formats. For formatting, I would recommend something like the following inside an Apply to each loop:
formatDateTime(items('Apply_to_each')['Last_Update'], 'F')
The 'F' modifier returns datetime in the format of 'Monday, June 15, 2009 1:45:30 PM', so if you just wanted Month and Day, you can split the result with the delimiter as space and index it to the 2nd item in the resulting list:
split(formatDateTime(items('Apply_to_each')['Last_Update'], 'F'), ' ')[1]

Let me know if this makes sense and if you have any other questions!
rwin45 8 on at

Like
a
(0)

Report
Copy link

Link copied!

i'm sorry I have just started learning power automate over the past couple months, so I am not too savvy.

I've only used Apply to Each loops with a condition that works through excel rows. I'm not quite sure how to use one to work through an array. Could you please elaborate a little?
Verified answer

abowen 222 on at

Like
a
(2)

Report
Copy link

Link copied!

Not a problem!

So in between your 'Sort Array' and 'Create HTML Table' actions in your second screenshot, I would create an 'Apply to each' action and an 'Initialize variable' action (which will be an array, and it can be empty). The input for the 'Apply to each' will be your sorted array, which should be labeled under your Sort Array Compose(?) action. It will look like this so far:

Now this 'Apply to each' action will loop through your array and we want it to pull the datetimes and change their format, which I believe they are stored under the column 'Last Update'. So we can then append the new array with your old, unchanging "Site Name" column and values, and then the updated DateTimes with proper formatting. See the following:
Once this 'Apply to each' finishes, you should be good to go with your shiny new array! Be careful on which array to finally use in your HTML table.

Please let me know if I can help further!
rwin45 8 on at

Like
a
(0)

Report
Copy link

Link copied!

You are brilliant.

Took me a second to figure out the formula needed 'Apply_to_each_2', but once I did that it worked perfectly. Thank you so much