Odata Date filters when using Power Query

[alisag]

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... 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.

[JWolmanSymplr]

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)? 

[dhPowerBI4]

@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.

[alisag]

Thank you!! @collinsg , this worked. 😊

[collinsg · Accepted solution]

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.