How can I filter the output of a 'List rows present in a table' using the date and time in the sheet (in two separate columns) to filter and show only the rows where the date/time is less than two hours before the flow runs?

[DanJECC]

I need to filter a failed payments report (in .xlsx format) that is run every 2 hours, but only 2 of which per day are actually reviewed by a person. Each one will have a lot of overlap with the previous report that was created 2 hours before. I need find and extract all the rows that are 2 hours old or less, therefore the ones that are new that won't be on the previous report. I then need to count the number of rows that filter produces, and if there are more than say 2, send an email alert to management as that would indicate a problem payment processing.

The Excel document has (among others) two columns with date/time info, one labelled "CreateDate" and one labelled "CreateTime". Both are stored as integers; CreateDate is the Excel way of storing dates, the number of days between 01 Jan 1900 and the date in question; and CreateTime is hhmm with no separator, so 4:17pm will appear simply as "1617". The report runs chronologically, oldest at the top, newest at the bottom.

I had thought of trying to do a comparison between the newest report and the previous one and filtering out matches to leave just the unique items (which in theory would be just the new items), but that won't be practical; there may be older items appear at the top of the newest report (and not on the previous report) that are to be ignored. So it has to filter by date/time.

I have gotten so far as taking the attachment from an email and dumping into a Sharepoint folder, then converting the contents to a table, and then using the 'List Rows present in a table' action to format it into an array. Now I just need to figure out a way to utilise the date/time integers to filter by the last 2 hours.

I think the below strategy will work, but I don't know enough about expressions to build it.

1. Take the date integer and convert it to seconds, I'll first subtract a 120 years worth of days to make it a smaller number, lol.
2. Convert the time integer to seconds, that would require splitting the 4-digit number up into two 2-digit numbers and converting them to seconds separately.
3. Then add them all together to get the date and time on that row represented as the number seconds elapsed between 1 Jan 2020 and the date/stamp.
4. Repeat for the date/time the flow runs (or the file creation date/time stamp for the Excel file).
5. Subtract the first from the second to find the time difference in seconds.
6. If that number is less than 7200 (number of seconds in 2 hours), keep that row.
7. Count the number of rows remaining; if more a certain number, send an email.

Can anyone help with this, or point me in the direction of some tutorials that can help me learn how do achieve this?

[v-jefferni]

Hi @DanJECC ,

Could you please give an example of the two columns in the Excel, in Text or with screenshot?

Best regards,

[DanJECC]

Below are the column headers as they appear in Excel

and here is how the array appears

[v-jefferni · Accepted solution]

Hi @DanJECC ,

So, if you use the List rows present in a table action, you can select ISO 8601 date time format, and be easy to filter the Date columns. I have a table in Excel as the example:

In my flow, I have below actions:

In Convert time zone action, I subtract 2 hours from current time:

addHours(utcNow(),-2,'yyyy-MM-dd hh:mm')

In List rows action, I use Filter query as below, it gets the records that are yesterday if current time is earlier than 2 AM, otherwise it gets todays records:

ReminderDate eq '@{if(greaterOrEquals(int(formatDateTime(body('Convert_time_zone'),'HH')),2),formatDateTime(body('Convert_time_zone'),'yyyy-MM-dd'),addDays(body('Convert_time_zone'),-1,'yyyy-MM-dd'))}'

In Filter array, I use the criteria to filter outputs from List rows, to get records that are in the recent two hours:

@greaterOrEquals(concat(formatDateTime(item()['ReminderDate'], 'yyyy-MM-dd'), ' ', take(item()['Time'], 2), ':', skip(item()['Time'], 2)), formatDateTime(body('Convert_time_zone'), 'yyyy-MM-dd HH:mm'))

Result of Filter array shown below, as you can see, I succeeded to get the only one record met the condition:

Best regards,

[DanJECC]

Sorry for the delayed response, I have been on leave for the last week.

Thanks for your answer, I feel we are close. The filter query on the List rows action is working, there were 11 items with today's date in them in the previous version of the report, and they were all retrieved. I updated the file the flow is referencing to the latest copy of the report, now with 15 for today, and the filter query in the List rows action is grabbing all of the 15.

Previously while testing sometimes the List rows output is for the previous day instead of today, I'm not sure what I'm changing that is causing it to sometimes be today and sometimes yesterday.

The Filter array action is not working as intended. Originally it was giving an output of just [], now it is showing all 15 items for today in both inputs and outputs, despite only 5 being less than 2 hours old.

I figured it out, it was the Convert time zone action, I didn't have it configured correctly. It is working now! Thank you.