cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter Query of Sharepoint rows between dates (dates specified in column)

Hi All,

 

I'm trying to automate sending out an excel sheet of rows filtered by a date column.

 

I want to include any row that has a value of the last 7 days.

Here's what I've tried:

 
 

 

But i keep getting this error :

 

The expression "2020-55-30" is not valid.
clientRequestId: 3e7e28fe-b34a-4ec3-9cc9-80ba03e5bf8a
serviceRequestId: 3e7e28fe-b34a-4ec3-9cc9-80ba03e5bf8a

 

 

Any help to filter the values by the last 7 days would be really helpful!

 

otsharepoint.PNG

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

Hi

THis post explains how to filter items from a SP list whose DueDate is greater than today, where DueDate is date column:

https://powerusers.microsoft.com/t5/Building-Flows/FLOW-Get-Items-Filter-SharePoint-list-on-Date/td-...

 

This second post explains how to calculate a date by adding, substracting days from today, and change date format:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Add-days-to-time-stamp/td-p/93096

 

Based on this approach, you need to filter items from a SP list whose DueDate is greater than 7 days ago.

I would try to insert the following expression...

formatDateTime(addDays(utcnow(),-7),'yyyy-MM-dd')

in your Odata Query

DueDate gt 'YOUR EXPRESSION HERE'

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

The screenshot is here:

 

otflowscreenshot.PNG

Highlighted
Dual Super User III
Dual Super User III

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

Hi

THis post explains how to filter items from a SP list whose DueDate is greater than today, where DueDate is date column:

https://powerusers.microsoft.com/t5/Building-Flows/FLOW-Get-Items-Filter-SharePoint-list-on-Date/td-...

 

This second post explains how to calculate a date by adding, substracting days from today, and change date format:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Add-days-to-time-stamp/td-p/93096

 

Based on this approach, you need to filter items from a SP list whose DueDate is greater than 7 days ago.

I would try to insert the following expression...

formatDateTime(addDays(utcnow(),-7),'yyyy-MM-dd')

in your Odata Query

DueDate gt 'YOUR EXPRESSION HERE'

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Frequent Visitor

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

@efialttes  thank you for the reply!

 

I tried implementing your solution but it still doesn't work:

 

otflowfail.PNG

otproof.PNG

 

Can you spot something I'm doing that is incorrect?

 

Thanks again for the help!

Highlighted
Dual Super User III
Dual Super User III

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

Hi

I think the problem is on the ODATA Filter: either the column name (You called it 'OverTime Start', right?) or the single quotes that shall be at the beginning and at the end of the expression (on the screenshot you just shared I am not sure if you added the closing quote)

 

Why the column name? Because if you define Column Names with special characters like spaces, Sharepoint will translate them internally and Flow relays on the SP Column internal name.

If you called the column OverTime Start I would try to define the ODATA Filter with OverTimeStart0, but not 100% sure this will be the internal translation. If not, either you can hover the mouse over the 'OverTime Start' dynamic content you already added in 'Create CSV' to get 'OverTime Start' internal column name or... remove temporarily the ODATA filter, execute the flow once and inspect the output of 'Get items' and search for 'OverTime Start' internal Column Name

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

Re: Filter Query of Sharepoint rows between dates (dates specified in column)

Works!

 

You were right the column name was causing problems!

 

Thanks again!

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (6,913)