Forum Discussion
filter table to current month + 2
Is it possible to filter a table to only include rows for current month + 2...i.e. I don't want current month, or next month, but the month after that
My data connector is ODATA (I believe it to be V3)
Using the query editor...
= Table.SelectRows(#"Sorted Rows", each Date.IsInNextMonth([Date]) ) Gives me next month
= Table.SelectRows(#"Sorted Rows", each Date.IsInNextNMonths([Date], 2) ) Gives me next month and the month after that
I want to skip next month and just select the following months data
I thought this might work:
= Table.SelectRows(#"Sorted Rows", each Date.IsInNextMonth( Date.AddMonths([TimeByDay],1) ) )
But I just get an error...
DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (Unable to create platform context. Please ensure that you have Log On Permissions on the server)
Details:
DataSourceKind=OData
DataSourcePath=https://mysite.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSet
SPRequestGuid=481b329e-d0d5-4000-79cf-c576987e49c0
Url=https://mysite.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSet?$filter=TimeByDay ge datetime'2016-01-01T00:00:00' and AssignmentWork gt 0&$select=AssignmentId,AssignmentWork,ProjectId,ResourceId,TaskId,TaskName,TimeByDay&$skiptoken=guid'4ad55920-b8a9-e711-96a7-4851b7dcbd4b',guid'b8d85920-b8a9-e711-96a7-4851b7dcbd4b',datetime'2017-06-14T00:00:00'
2 Replies
- MarcelBeug
Community Champion
This would be my suggestion:
= Table.SelectRows(#"Sorted Rows", each not Date.IsInNextMonth([Date]) and Date.IsInNextNMonths([Date],2))
- AndyTrezise
Advocate IV
Thanks for the suggestion but when I include not I get the following error:
= Table.SelectRows(#"Sorted Rows", each Date.IsInNextNMonths([Date], 2) and not Date.IsInNextNMonths([Date], 1))
DataSource.Error: OData: A unary operator with an incompatible type was detected. Found operand type 'Edm.DateTime' for operator kind 'Not'.
Details:
DataSourceKind=OData
DataSourcePath=https://mysite.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSet