Forum Discussion
Advocate IIODATA with Or operator
Hello!
I'm trying to create a filter in my dataflow (that connects to an ODATA) that searches for all rows where the date columns RDate or PODate or RXDate are greater than 01/01/2024. However, there seems to be an error in my Or operator, because this filter does not return all rows that meet this condition of one of these three columns being greater than 01/01/2024.
This is my code:
let
Source = OData.Feed("https://api.xyz.com/MyTable?$filter=(RDate gt 2024-01-01T23:59:59.99Z) or (PODate gt 2024-01-01T23:59:59.99Z) or (RXDate gt 2024-01-01T23:59:59.99Z)", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)])
in
Source
It's as if the filter works for some rows and not others. When I remove the Or operator and filter on just one column (RDate, for example), the filter works perfectly. But when I want to filter using Or for these three columns, the filter leaves out some data.
Does anyone know what I'm doing wrong in the code?
Hi nok , Thank you for reaching out to the Microsoft Community Forum.
The problem likely stems from an API-specific behaviour or limitation in handling complex or conditions.
Please try below:
let
BaseUrl = "https://api.xyz.com/MyTable",
RDateQuery = OData.Feed(BaseUrl & "?$filter=RDate ne null and RDate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
PODateQuery = OData.Feed(BaseUrl & "?$filter=PODate ne null and PODate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
RXDateQuery = OData.Feed(BaseUrl & "?$filter=RXDate ne null and RXDate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
Combined = Table.Combine({RDateQuery, PODateQuery, RXDateQuery}),
Result = Table.Distinct(Combined)
in
Result
some twerks may be needed.If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
7 Replies
- lbendlin
Super User
Use the in() operator instead.
v-hashadapuCommunity Support
Hi nok , Thank you for reaching out to the Microsoft Community Forum.
Please try below:
let
Source = OData.Feed(
"https://api.xyz.com/MyTable?$filter=(RDate ne null and RDate gt 2024-01-01T00:00:00Z) or (PODate ne null and PODate gt 2024-01-01T00:00:00Z) or (RXDate ne null and RXDate gt 2024-01-01T00:00:00Z)",
null,
[Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]
)
in
Source
I also suggest checking for inconsistent date formats in the source data and testing each condition separately to confirm which column’s data is problematic.If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
nokHi v-hashadapu . Thanks for the reply!
I tried using the code you provided and it still brings less data than it should. When I test the filter on each column separately, the filter works perfectly. It only gives problems when I try to combine the filter on the three columns using Or operator.
It's as if the error is in the Or operator itself, which seems very strange to me.- v-hashadapu
Hi nok , Thank you for reaching out to the Microsoft Community Forum.
The problem likely stems from an API-specific behaviour or limitation in handling complex or conditions.
Please try below:
let
BaseUrl = "https://api.xyz.com/MyTable",
RDateQuery = OData.Feed(BaseUrl & "?$filter=RDate ne null and RDate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
PODateQuery = OData.Feed(BaseUrl & "?$filter=PODate ne null and PODate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
RXDateQuery = OData.Feed(BaseUrl & "?$filter=RXDate ne null and RXDate gt 2024-01-01T00:00:00Z", null, [Implementation="2.0", Timeout=#duration(0, 1, 30, 0)]),
Combined = Table.Combine({RDateQuery, PODateQuery, RXDateQuery}),
Result = Table.Distinct(Combined)
in
Result
some twerks may be needed.If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
- lbendlin
Ah, right, I missed the gt part. I would think that you might be pushing OData beyond what it can do. it's possible that the combination of or + gt is not supported.
