Forum Discussion

alisag's avatar
alisag
New Member
3 years ago
Solved

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,ToCurre...
Need Help
  • collinsg's avatar
    collinsg
    3 years ago

    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.

JWolmanSymplr's avatar
JWolmanSymplr
Resolver I
2 years ago

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's avatar
dhPowerBI4
Regular Visitor
2 years ago

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's avatar
    JWolmanSymplr
    Resolver I
    2 years ago

    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.