Fix Power Automate Date Filter Returning Zero SharePoint Items
Question details
The user needs to resolve an issue where a Power Automate flow returns zero SharePoint list items when filtering by a date range.
- Product
- Power Automate
- Device & OS
- not provided
- Scenario
- Counting SharePoint Marketing list items submitted in the past seven days to write the total to an Excel file.
- Observed behavior
- The flow executes successfully but returns a count of zero items, despite matching records existing in the SharePoint list.
Double-check the exact internal name of your SharePoint date column, as it may differ from the display name, and ensure your Power Automate flow has the correct permissions to read the target SharePoint list.
Format the Date Query to Match SharePoint's Stored Format
Ensure the date and time format in your OData Filter Query exactly matches how SharePoint stores the date, including the time component.
SharePoint stores dates with specific time components. If your OData filter query doesn't match this format or misses the time component entirely, the filter will fail to match existing records and return zero items.
Open your Power Automate flow in edit mode and click on the 'Get items' action configured for your SharePoint list.
In the 'Filter Query' field, enter your column's internal name followed by the 'gt' (greater than) or 'ge' (greater than or equal to) operator.
In the dynamic content expression box, use the 'formatDateTime' function. For exactly 7 days ago, input: formatDateTime(addDays(utcNow(),-7),'MM-dd-yyyy HH:mmtt').
Ensure that the dynamic expression you just inserted is wrapped in single quotes within the Filter Query field, for example: Created gt '[Your_Expression_Here]'.
Verify the SharePoint Column's Internal Name
The OData filter requires the internal column name, not the display name, which might have changed or contain encoded spaces.
Edit and Analyze Your Exported Data with WPS Office
If your Power Automate flow writes list item counts or reports to an Excel spreadsheet, WPS Office provides a lightweight, fully compatible, and free alternative to Microsoft Office. Easily open, analyze, and format your exported .xlsx files without expensive subscriptions.
Frequently Asked Questions
Why does my OData filter query say the column does not exist?
This happens when you use the display name of the SharePoint column instead of its internal name. Check the list settings URL in SharePoint to find the correct internal field name.
What operator should I use to filter dates in the past in Power Automate?
Use the 'ge' (greater than or equal to) or 'gt' (greater than) operator when filtering for items created after a specific past date, such as retrieving all items from the last 7 days.
Do I need to include single quotes around the date expression in Power Automate?
Yes, in the OData Filter Query field, the evaluated date string must be wrapped in single quotes, otherwise the syntax will be invalid.
How do I handle time zone differences in Power Automate date filters?
SharePoint stores dates in UTC. Use the 'convertFromUtc' and 'convertToUtc' expressions in Power Automate to ensure your filter matches the correct local time period before querying the list.
