Power Automate Get Items Filter Query Date

Are you using the “Get Items” flow action in Power Automate? In this tutorial, I will explain how to use a filter query based on the date in Power Automate Get items flow action, with a few examples related to “Power Automate Get Items filter query date“.

To filter bulk items from a SharePoint list based on Date, a user can use Power Automate Odata filter query date. Example: ResignationDate eq’@{formatDateTime(utcNow(), ‘yyyy-MM-dd’)}’.

Overall, I will show you the below examples related to Power Automate Get Items Filter Query Date:

  • Power Automate OData Filter Query Equals Today Date
  • Power Automate OData Filter Query Less than Today Date
  • Power Automate OData Filter Query Greater than Today Date
  • Power Automate OData Filter Query Created Date
  • Power Automate OData Filter Query Last 7 Days
  • Power Automate Flow Query Filter Date Range
  • Power Automate Filter Query Date Empty

Power Automate OData Filter Query Equals Today Date

Here, we will use the Power Automate Get Items to filter items from a SharePoint list that is equal to Today’s date.

Here I have a SharePoint list having a few columns like below:

For that, I will take a SharePoint list named ‘Employee Resignation Log‘ with ‘Resignation Date’ as the date and time column. If the ‘Resignation Date’ has a current date, then it will filter out all the items that are showing today’s date.

Power Automate ODATA filter query date column todays date

Follow the below instructions:

  • Create an ‘Automated cloud flow’ with a trigger ‘When an item is created or modified’ and provide the below details:
    • Site address: Provide a specific site address from the drop-down.
    • List Name: Select a specific list from the drop-down.
SharePoint get items for Todays date filter query
  • After that, add the ‘Get items‘ flow action and set the below details:
    • Site address: Select a specific site address from the drop-down.
    • List Name: Select a specific list from the drop-down.
    • Filter Query: Provide the condition as placed below code:
ResignationDate eq'@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'
SharePoint get items for Todays date filter query by Power Automate
  • Then add Create HTML table and take a custom table to add values dynamically as shown below:
Power Automate filter items with query today
  • Once the flow has been created, Click on Save and Test.
  • Open the ‘Create HTML Table’ to view the output from the Power Automate filter query date as of today.
Power Automate Filter Query Date equals Today's Date

This is how to use a Power Automate filter query date comparison with today’s date to retrieve data from the SharePoint list.

Power Automate OData Filter Query Less than Today Date

In this section, I will show you an example based on the Power Automate filter query date less than today.

Here, I have used the same SharePoint list placed above to filter list items that are less than today’s date.

Follow the above-mentioned step-by-step process, but changes are required only in the ‘Get items’ flow action.

1. In the ‘Get items’ flow action, set parameters like Site Address, List Name, and Filter Query.

ResignationDate lt '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'

Where [lt] refers to less than the operator in Power Automate filter query syntax.

Odata filter query date less than today's date Power Automate

2. In the flow, the ‘Create HTML table’ will display the list of SharePoint items that are less than today’s date.

Odata Filter Query Power Automate date less than today's date

This is how to work with the Power Automate odata filter query date less than today.

Power Automate OData Filter Query Greater than Today Date

In the same way, I will discuss an example based on the Power Automate OData filter query greater than today’s date.

For implementing an Odata filter query date greater than today’s date in a Power Automate flow, I will use the same SharePoint list containing ‘Resignation Date’ as a date and time column.

Follow the similar steps and change the filter query syntax like below:

1. Open the ‘Get items’ flow action and add the filter query condition in the filter query parameter.

ResignationDate gt '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'

Here, [gt] refers to greater than in Power Automate filter query syntax.

Filter query Odata date greater than today in Power Automate

2. When the flow runs successfully, the HTML table that displays the SharePoint list items whose ‘Resignation Date’ is greater than today.

Power Automate Odata Filter Query Greater than today's date

This is how to use Power Automate to get items filter query date greater than today’s date.

Power Automate OData Filter Query Created Date

Similarly, let me explain to you the example based on the get items filter query by created date less than the current date.

To implement it, I have taken a SharePoint list named ‘Product- Restock List‘ having a default created column in it as shown below:

Power Automate filter items with created date

In the above SharePoint list, the Power Automate Odata filter query will fetch all items that have a ‘Created date’ that is less than today’s date.

  • Create a manual trigger flow in a Power Automate.
  • Add the ‘Get items‘ flow action and provide the below details like Site Address, List Name, and, Filter Query.
Filter Query: Created lt '@{startOfDay(utcNow(),'yyyy-MM-ddTHH:mm:ssZ')}'

Here, (lt) means – less than in the syntax of the Power Automate filter query.

Power Automate Filter Query get items based on Created Date
  • Then, add ‘Create HTML table‘ as custom columns and provide column values dynamically as shown below:
Power Automate Get items filter query created date
  • After the flow has been created, click on Save and Test.
  • When the flow runs successfully, the output will display inside html table that list of items whose Created date < Today Date.
Power Automate Get items filter query created date less than today

This is how to use Power Automate OData filter query created date less than today’s Date.

Power Automate OData Filter Query Last 7 Days

In this section, I will demonstrate the Power Automate filter query date 7 days less than today’s date.

Here, I will explain how to use the Power Automate Odata filter query date in a flow to get items from a SharePoint list i.e., the last seven days.

To implement it, I have taken a SharePoint list named ‘Events‘ with ‘Event Start Date’ as a date and time column as you can see from the below screenshot.

Power Automate OData filter query last 7 days

Example: Based on the Power Automate flow get items filter query, the flow will filter the items of the last 7 days before the current date. If the Event Start Date has values of the last 7 days from today, it will filter out those items from a SharePoint list.

Follow the mentioned points to create a flow.

  • Create an ‘Automated cloud flow’ in a Power Automate that will trigger automatically.
  • Next, add the ‘Get items‘ flow action and provide the below properties:
    • Site address: Select a specific SharePoint site from the drop-down.
    • List Name: Select a List name from the drop-down.
    • Filter Query: Add filter query as placed in the below code.
EventStartDate gt '@{formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')}'

Where [gt] means greater than the operator in the Odata query syntax of Power Automate.

Power Automate filter Date for last 7 days
  • Next, add Create HTML table as custom columns and provide column values dynamically as shown below:
How to filter last 7 days date in Power Automate
  • Now, it’s time to save and test the flow -> click on Save and Test.
  • Then, open the html table to see the list of SharePoint items that are prior 7 days to the current date.
Power Automate Get Items Filter Query last seven days

This is how to obtain data for the last seven days using the Power Automate filter query date comparison inside a flow.

Power Automate Flow Query Filter Date Range

In this example, I will discuss obtaining the Odata filter Query date range in Power Automate.

Let us build an ‘ Automated cloud flow’ to get an understanding of fetching data [From date – To date] using a filter query in a Power Automate.

Within the SharePoint list (Task List), I have taken two columns with date and time data types such as ‘Start Date’ and ‘Task Due Date’ as shown below.

Power Automate Flow query filter date range

Example: If the Start Date is less than or equal to the current date and the Task Due Date is greater than or equal to the current date -> Fetch all the SharePoint list items that are in between that given condition.

ConditionActions
‘Start Date'<= Today’s date &
‘Task Due Date’ >= Today’s date
Fetch all the SharePoint list items that are in between that given condition.

Follow the below-mentioned instructions:

  • Open a Power Automate Home page to create ‘Automated cloud flow’.
  • After that, add the ‘Get items‘ flow action and provide below details such as:
    • Site address: Select a site address.
    • List Name: Select a List name from the drop-down.
    • Filter Query: Add filter query as placed below code.
(StartDate le '@{utcNow()}') and (TaskDueDate ge '@{utcNow()}')

Where [le] means less than or equal to and [ge] means greater than or equal to operators in the syntax of Power Automate filter query.

Power Automate using a filter query between two dates
  • Then, add Create HTML table with custom values dynamically as shown below screenshot:
Filtering based on a date range in Power Automate
  • Once the flow has been created, Click on Save and Test the flow manually.
  • Then the outputs of an HTML table will show all the SharePoint list items that will satisfy the filter query condition of a date range.
Power Automate Odata Filter Query Date Range

This is how to work with get items Odata filter Query date range in Power Automate.

Power Automate Filter Query Date Empty

However, I have discussed many examples based odata filter query date. Now, let me explain to you the final example of Power Automate filter query date being equal to ‘Null’.

To implement this Odata filter query date empty in a Power Automate flow, follow the instructions below.

Now, I will use a SharePoint list named ‘Events’ that consists of ‘Event End Date’ as a date and time column.

Power Automate Odata filter query date empty

Based on the Power Automate filter query, I will check whether the ‘Event End Date’ contains null values or not.

Follow the above-mentioned step-by-step instructions.

1. In the ‘Get items’ flow action, add the Site Address and list Name along with the Filter Query.

EventEndDate eq null

Where ‘null’ should not be in between single quotations in filter query syntax.

Power Automate filter query date equal to null

2. The output of the HTML table displays all the SharePoint list items that have ‘Event End Date’ with null or empty values.

Power Automate Filter query date equals null

This is how to deal with Power Automate filter query date column ‘null’ or empty’.

Conclusion

I hope this tutorial will help you with filter query date Power Automate examples. We saw many scenarios on Power Automate Odata filter query dates, like the filter query date column based on today’s date and Power Automate filter query date less than today. Also, Odata filter query date greater than today’s date in Power Automate.

I have also shown you conditions like get items filter query filter prior 7 days to today’s date. In addition, the filter query created date and Odata filter query date range, Power Automate filter query date column is equal to null values.

You may also like: