Power Automate Filter using From and To Date - Filter SharePoint list by date range

Power Automate Filter using From and To Date: Filter SharePoint list by date range Power Automate

No comments

 571 total views,  11 views today

In this “Power Automate filter” article, we will learn how to filter SharePoint list items based on the “From” and “To” created dates using a Power Automate OData filter query.

Many times, it is a very much needed requirement in the SharePoint big data management system that we need to retrieve data from the given two dates (from and to date).

Power Automate Filter using OData filter query (From and To Date Range)

This is the complete flow that will retrieve data from SharePoint Online’s document library from the given two dates.

Power Automate Filter using OData filter query - Date Range
Power Automate Filter using OData filter query – Date Range

Flow Explanation: Filter SharePoint list by date range Power Automate

Below is a step-by-step explanation of the flow:

Variable declaration section

Create two string variables to hold the values of “From Date” and “To Date.”

Filter SharePoint list by date range Power Automate
Filter SharePoint list by date range Power Automate

The date format is: YYYY-MM-DDT00:00:00Z

Notes:

  • In the “From Date,” enter the lower date, for example, 2022-12-01, and in the “To Date,” enter the higher date or later date, for example, 2022-12-05.
  • Leave the “T00:00:00Z” as is; do not change this.

OData Date Filter Query in Get Items Power Automate

Add the Get Items action and enter your site address, list name, or document library name.

OData Date Filter Query in Get Items Power Automate
OData Date Filter Query in Get Items Power Automate

Enter the below in the Filter query:


Created ge 'varFromDate' and Created le 'varToDate'

 

Notes:

  • In this demo, the above query will retrieve all data from a SharePoint document library created from December 1 through December 5.
  • Top count set to 5000; this will handle the threshold limit issue if your document library or list contains more than 5000 items.

 

Threshold Configuration: The threshold should be a number greater than 0 and less than or equal to 100000.

Additional configuration is needed to handle more than 5000 items in your source document library or list.

Click on the three dots.

Click on the “Settings” gear icon.

 

Get items settings configuration in Power Automate
Get items settings configuration in Power Automate

Then, enable pagination. By default, this will be in disabled mode.

Enable pagination and threshold limit in Power Automate Get Items query

Enable pagination and threshold limit in Power Automate Get Items query

Click on the “Done” button.

Notes:

  • The threshold should be a number greater than 0 and less than or equal to 100000.
  • This configuration is needed when you have more than 5000 items or documents in your list or library.
  • To avoid the flow timeout error, you can enter duration as “P1D” for the timeout configuration.

By the way, what is pagination in Power Automate Get Items query?

Retrieve items to meet the specified threshold by following the continuation token. Due to connector’s page size, the number returned may exceed the threshold.
Pagination.

Flow Demo: Filter SharePoint list by date range Power Automate

Now, let’s execute the flow manually.

Filter SharePoint list by date range Power Automate flow demo
Filter SharePoint list by date range Power Automate flow demo

At the end of the flow, we have added a compose data operation action to find the filter query length of Get Items.

The flow ran successfully.

Expanding the compose action, we can see that there are 3 items returned by the Get Items query.

Compose data operation action to find the filter query length of Get Items
Compose data operation action to find the filter query length of Get Items

Filter SharePoint list item by date range View

We can also filter the SharePoint list item by date range by creating a view. Below is the filter condition for that.

Filter SharePoint list item by date range View
Filter SharePoint list item by date range View

Notes:

  • The created date is greater than or equal to “From Date.”
  • The created date is less than equal to “To Date.”
  • We have also applied filters to the content type column to ensure that the filter query retrieves only documents.
  • The date format for the filter is MM/DD/YYYY.
  • You need to add indexing to the “Created” column from the document library or list settings.

Summary: Power Automate filter using From and To date (Power Automate oData Filter Query)

Thus, in this article, we have learned the following with respect to Power Automate’s OData filter query:

  • How to use Power Automate to retrieve SharePoint list items or documents based on a date range filter query.
  • How do you make a custom view in a SharePoint document library or list to retrieve items or documents based on the created From and To date ranges?
  • How to retrieve more than 5000 items using the Power Automate Get Items filter query
  • How to enable pagination in Power Automate Get Items.
  • How to configure the threshold limit in Power Automate Get items settings.

See Also: Power Automate Articles Hub (with real-time example)

If you like our articles, please appreciate our efforts by liking and sharing them with your friends and colleagues. Please join the mailing list to get our articles in your inbox as and when we publish them.

Happy automating and learning!

About Post Author

Do you have a better solution or question on this topic? Please leave a comment