Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
alisag
New Member

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

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

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.

View solution in original post

5 REPLIES 5
JWolmanSymplr
Resolver I
Resolver 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

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
New Member

Thank you!! @collinsg , this worked. 😊

collinsg
Super User
Super User

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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors