If you are looking for odata date filter expressions in Get Items in Power Automate, check out this complete tutorial. Here, I have taken two real examples of the Power Automate odata date filter last 30 days and the Power Automate odata date filter for the next 30 days.
Recently, I was working on a SharePoint Online list, where we wanted to filter records based on Date conditions in Power Automate. We wanted to get the last 30 days’ items from the SharePoint list based on a date column inside a flow.
Here is the SharePoint list named ‘Appointment Request’ with different columns like the one below:
| Column Name | Data Type |
| Appointment ID | Single line of text ( Used the default Title column) |
| Full Name | A person or Group Column |
| Appointment Date | Date and Time Column |
| Preferred Date | Date and Time Column |
| Assigned Manager | A person or Group Column |
Below is what the SharePoint list looks like with some data.
Here, we will fetch the last 30 days’ record based on the Appointment Date column. We are required to fetch records of the last 30 days starting from Today’s Date (05-10-2023) up to 05-09-2023 from the SharePoint list.
Finally, we wanted to email the list of records to the assigned manager using the flow in Power Automate.
For this, I will create an instant cloud flow that will trigger manually. Let’s dive in.
odata date filter last 30 days in Power Automate
1. Navigate to https://make.powerautomate.com/ and click on + Create -> select “Instant cloud flow”. Then, provide a flow name, and choose the trigger as “Manually trigger a flow”. Click on Create.
2. Once the flow is created, add a “Get items” flow action to it. This will get all the items from the SharePoint list. Here, provide the details below:
- Site address: Select a site address from the drop-down.
- List Name: Select a SharePoint list from the drop-down.
- Filter Query: Add the below code in the Filter query.
AppointmentDate ge '@{formatDateTime(addDays(utcNow(),-30),'yyyy-MM-dd')}' and Created le '@{utcNow()}'Here, ge for greater than or equal to and le for less than or equal to. Check the figure below:
The above code will give the last 30 days’ record from the SharePoint list.
Next, we wanted to send an email with the list of records.
3. Then, Add the “Create HTML table” flow action and select the below values:
- From: Select value from Get items of dynamic content.
- Columns: Select Custom table from drop-down.
| Header | Value |
| Appointment ID Full Name Appointment Date | Select values from the dynamic content under Get items |
4. After that, add a Send an email (V2) action and provide the below details:
- To: Select the email address of a manager from dynamic content.
- Subject: Enter a subject for an email.
- Body: Give detailed information regarding email as placed in the code below.
When I have selected the email address in the To parameter, Apply to each will be added automatically.
Hello @{items('Apply_to_each')?['AssignedManager/DisplayName']},
Hope you are doing well!
This is the list of appointments received in the last 30 days. Please check out this, list for available consultation timing dates.
Table:
@{body('Create_HTML_table')}
Regards
Hospital Management
5. Now, the flow is ready, and we can test it. Save the flow and Click on Test -> Manually to test flow.
6. When the flow runs successfully, it will display a notification as ‘Your flow ran Successfully‘ and send an email notification as shown below:
7. The manager will receive an email having the last 30 days’ records from the SharePoint list using Power Automate.
This is how to use the Power Automate OData filter query to filter records of the last 30 days from a SharePoint list.
Power Automate odata date filter for next 30 days
Based on the Date in Power Automate, I will show you how to filter data for the next 30 days.
I will use the same list as above, and I will use this time the Preferred Date column.
Let us extract the next 30 days’ records from the SharePoint list based on the Date and Time column using a filter query in Power Automate flow.
The preferred date column contains different month dates. But, we only need to fetch the items for the next 30 days starting from Today’s date (01-10-2023) to (30-10-2023).
Follow the below step-by-step instructions to create an instant cloud flow:
1. Click App Launcher -> Select Power Automate and click on +Create -> Select “Instant cloud flow”. In the next window, provide a flow name, choose “Manually trigger a flow” and click on Create.
2. When the trigger is added to the flow studio, add the “Get items” flow action, which will extract all the SharePoint list items based on the given condition in the filter query. Set the details below:
- Site address: Select a SharePoint site from the drop-down.
- List Name: Select a SharePoint list from the drop-down.
- Filter Query: Place the below code in the filter query,
(PreferredDate le '@{formatDateTime(addDays(utcNow(),30),'yyyy-MM-dd')}') and (PreferredDate ge '@{startOfMonth(utcNow())}')Where ‘le’ refers to – Less than or equal to and ‘ge’ refers to – Greater than or equal to in Power Automate.
Now, the expression will give the records of a SharePoint list for the next 30 days.
After that, we have to make a table of that list of items and send an email to the assigned manager.
3. In this step, add a “Create HTML table” and select below values:
- From: Select value from Get items of dynamic content.
- Columns: Select a custom table from the drop-down.
| Header | Value |
| Appointment Id Preferred Date Appointee Name | Select values from the dynamic content of Get items. |
4. After that, add a Send an email (V2) flow action and set the below properties.
- To: Select an email address from dynamic content.
- Subject: Give a subject for an email.
- Body: Provide detailed information about the email as placed in the code below.
Apply to each loop will be added automatically when I have selected the “To” parameter in the send email (V2) action.
Hello@{items('Apply_to_each')?['AssignedManager/DisplayName']} ,
Hope you are doing well!
This is the list of appointments received in the current 30 days. Please check out this, list for available consultation timing dates.
Table:
@{body('Create_HTML_table')}
Regards
Hospital Management
5. Once the flow is ready, save the flow by clicking on Save. Then, click on Test -> Manually test the flow.
6. When the flow runs successfully, the Assigned manager will receive an email with the next 30 days’ records from a SharePoint list.
This is how to use a Power Automate OData filter query to fetch items from a SharePoint list that have the next 30 days’ records.
Conclusion
I hope this tutorial helped you to get SharePoint items from the last 30 days by applying an OData filter query condition in a Power Automate flow.
Along with this, step-by-step, I also explained how to fetch SharePoint list items by using the Power Automate odata date filter for the next 30 days.
You may also like:
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.