Filter Date using Filter Array / HTML Table - Attach Filtered table to Outlook email

[mjubin]

Hello!

Having a little trouble with getting power automate to filter my data.

The filter array is looking for the start time column to be less than my listed "Get Past Time" - which is 1 day.

I have a Filter Array to a created HTML table to grab the filtered table and list it back as an email. It seems to fail both filtering the data and the attaching as a xlsx file type.

In short - trying to get my file to filter and send out with items created the past 24 hours:

I am much appreciative of any solutions!

[grantjenkins · Accepted solution]

Is there a reason why you are using Get worksheets and Get tables? It looks like you are just selecting the actual Excel file and Table directly in List rows present in a table, so you likely don't need them.

What do you mean by the Filter array is failing? Can you provide the error message, etc.

Also, it's not easy to create an Excel file unless you're starting with an existing template.

If you are creating an HTML Table, then this would generally be added into the body of the email.

If you wanted to attach the data, then I'll have a look at creating a CSV Table that you can attach.

See my flow below that has both options. I'll go into each of the actions.

Get past time would be the same as yours. 1 day ago.

List rows present in a table gets all my rows. I would have suggested using the Filter Query here rather than the Filter array but can't do that if you have spaces in the headers in your Excel file which you do in this case.

Filter array looks similar to you one, so not quite sure why yours isn't working.

I then have the Create HTML table the same as yours.

And have added Create CSV table taking in the same input from Filter array.

Note that using the default Automatic will use all your Table headers, plus add a couple of internal fields which you may not want. In this case you can click on Show advanced options and select Custom, then map exactly what headers and values you want.

Lastly, in Send an email we have the HTML table in the body of the email and the CSV table as the output of the attachment, and what you want for the file name (including the extension .csv) for Attachments Name.

[ekarim2020]

Hi @mjubin ,

When you run get paste time, you'll get a date like the following:

2022-11-03T19:17:53.1442497Z 

So your Filter Array action will compare the Excel date with the past date, for example: 

11/03/22 15:06:07 = 2022-11-03T19:17:53.1442497Z 

and this will never evaluate to true as the date time formats are very different.

One solution is to format both past date and the Excel dates as yyyy-MM-dd and then perform the comparison.  For example:

 

In your case the action will be the Filter Array action and not the condition action as illustrated above.

The sample expressions are:

formatDateTime(body('Get_past_time'),'yyyy-MM-dd')
formatDateTime(parseDateTime('11/03/2022 15:06:07', 'en-US', 'MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd')

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.