Answered

Filtering out rows based on the date

(2) Share

Report

Posted on by FN-30071725-0

I need to filter out rows in a table based on the date in one of the columns from the flow whose picture I have attached. I am wondering what the best way to do it would be. A Filter array action maybe?

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (2)

Sort by

David_MA 14,485 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

This post shows you several ways to filter an Excel file on a date: Filter Excel Files Based On Date Using Power Automate

Was this reply helpful? Yes No
Verified answer

Pstork1 69,397 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

You've got two options.

1) Use an OData query in the List Rows action. However that is difficult when working with dates, especially in Excel because dates are stored as date and time so to filter on a date usually means filtering on a range that starts and midnight yesterday and goes to midnight today. That is difficult in OData.

2) Use a filter array either before or after the data select. Before the Select is more efficient since the select doesn't need to process as much. But if you filter after the data select you can use the select to isolate the date from the time and make the filter much easier.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
FN-30071725-0 62 on at

Like (0)

Report
Copy link

Link copied!

@Pstork1, thanks. I went ahead with the filter array option, but it doesn't give any output.

I am filtering out only those rows where the date is equal to or 2 days prior to today's date. The code I used was generated using copilot. It is below. Any suggestions on how I could fix this issue?

and(greaterOrEquals(item()?['Date'], formatDateTime(addDays(utcNow(), -2), 'yyyy-MM-dd')), lessOrEquals(item()?['Date'], formatDateTime(utcNow(), 'yyyy-MM-dd')))

Was this reply helpful? Yes No
Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

So you are trying to get things that are no more than 2 days old? If so, then that should be the right formula. Is Date the name of the field you are trying to filter on?

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
FN-30071725-0 62 on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 Correct, I am using it to get the rows where the date is not more than 2 days old. I used copilot to try and zero in on the reasons behind it, it said that there may be something wrong with the date format itself.

But when I run the flow and go into Inputs, I can see that the format is along the lines of "yyyy-MM-dd".

I am not sure what else I can do to diagnose the issue.

Was this reply helpful? Yes No
Verified answer

David_MA 14,485 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Please share a screen shot of what you are doing. If you followed the instructions in the link I posted, you would set up your filter like this:

The expression on the right is: addDays(utcNow(), -2, 'yyyy-MM-dd')

Copilot is telling you to use the date format yyyy-MM-dd because that is an ISO standard, which Power Automate requires for filter queries. Therefore, in the Excel action be sure that you have the date format set to ISO 8601:

By default, the Excel get rows action only returns the first 256 rows. If your spreadsheet has more than that, you need to enable paging.

Was this reply helpful? Yes No
FN-30071725-0 62 on at

Like (1)

Report
Copy link

Link copied!

@David_MA Thank you for laying that out so well. I messed up while inputting the filter query.

And thank you @Pstork1 for suggesting the filter array after select option. It indeed was a little easier to implement.

Was this reply helpful? Yes No