Filter by DateTime in Odata

[Anonymous]

Hi, 

I would need to filter by the column "CalendarDateId" in an OData feed, but I can't the filter to work. For example records where CalendarDateId >= 08/01/2022. I've tried several formats and different yy-mm-dd separators ("/" or "." or "-"), nothing works. How should the filter be written?

Br,

Chris

[Anonymous]

Hi @Jeanxyz 

Due to the size of the data and the fact that it is very slow to do any transformations in Power Query, I would need to filter the data in the Odata -query.

Br, Chris

[Anonymous · Accepted solution]

Found the solution, should be written like this "datetime'2022-08-01T00:00:00.000'

[Jeanxyz]

@Anonymous It looks the dateIDs are already recognized by Power BI as dates, can you filter the date column in Power query?

If that doesn't work, you will need to format the dateID into correct format before you can filter the dates.

step 1. convert date type to Text, user Transform-replace query to replace  ' 0.00.00' to blank

step 2. aftet that you should have the value in date format, select the whole column, right click and choose change type- using local, depending on the time format setup in your computer, you need to choose Date - English (United Kingdom) or English(United States)