Forum Discussion
Odata Date filters when using Power Query
Hello All,
I have the following query which is trying to fetch excahnge rates from Exchange Rate Table,
https://sandbox.operations.dynamics.com/data/ExchangeRates?$select=FromCurrency,ToCurrency,StartDate,EndDate,Rate&$filter= FromCurrency eq 'USD'
is it possible to filter data at the fetch time to get data only for last 2 days(StartDate)
Something like StartDate ge Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -2), "yyyy-MM-dd")
the above filter didn't worked.
Good day alisag,
If you wish to filter to include yesterday and the day before:
Table.SelectRows( #"Previous Step", each Date.IsInPreviousNDays( [Date], 2 ) )
If you wish to filter to include yesterday and today:
Table.SelectRows( #"Previous Step", each [Date] >= Date.AddDays( Date.From( DateTime.LocalNow() ), -1 ) ),
My date column is called "Date", substitute the name of your date column anywhere I use [Date].
These require your date column to be of type Date.
Hope this helps.
5 Replies
collinsgSolution Sage
Good day alisag,
If you wish to filter to include yesterday and the day before:
Table.SelectRows( #"Previous Step", each Date.IsInPreviousNDays( [Date], 2 ) )
If you wish to filter to include yesterday and today:
Table.SelectRows( #"Previous Step", each [Date] >= Date.AddDays( Date.From( DateTime.LocalNow() ), -1 ) ),
My date column is called "Date", substitute the name of your date column anywhere I use [Date].
These require your date column to be of type Date.
Hope this helps.
JWolmanSymplrResolver I
Having an additional step does not help in my case. I would like to filter in the OData call itself. And as it happens to work using the following syntax (
$apply=filter(DateValue ge " & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-102), "yyyy-MM-dd") & " and Date/DayOfWeek eq 5) the Dataflow will not save. I get the following error when I try to save my Dataflow on the Power BI Server: Can't save dataflow One or more tables references a dynamic data source
How can I filter in the Odata statement on a relative date (i.e. Today - 100 Days)?JWolmanSymplr Were you able to solve this? I have the same issue where I need the date filter to be applied in the OData call
- JWolmanSymplr
Not by doing anything date related with the initial OData feed call itself. I found another way to do it but I was then limited to a some JSON limitation. I have since given up and we have gone a different route to get this information using Mulesoft to call the OData calls and write the necessary information to a data warehouse. Really should be able to do date manipulation in the OData call itself.
