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.
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.”
The date format is: YYYY-MM-DDT00:00:00Z
- 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.
Enter the below in the Filter query:
Created ge 'varFromDate' and Created le 'varToDate'
- 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.
Then, enable pagination. By default, this will be in disabled mode.
Enable pagination and threshold limit in Power Automate Get Items query
Click on the “Done” button.
- 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.
Flow Demo: Filter SharePoint list by date range Power Automate
Now, let’s execute the flow manually.
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.
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.
- 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!