Power Query with a Date.AddDays

[rjs2]

I am trying to write a query when it will only pull dates 60 days older than today, so I dont have to change the query when I refresh it

This is what I have  I get the error below

WHERE  ""Table"".""Date"" <= Date.AddDays(Date.From(DateTime.LocalNow() as datetime),-60)

This is what I originally had and got no errors

WHERE  ""Table"".""Date"" <= TO_DATE ('14-12-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Error:

DataSource.Error: Oracle: ORA-00936: missing expression

Details:
    DataSourceKind=Oracle
    DataSourcePath=reni2.reninc.com
    Message=ORA-00936: missing expression

    ErrorCode=-2146232008

[az38]

Hi @rjs2 

it is a Power QUery style and could be use only in Power Query (Advanced editor)

 Date.AddDays(Date.From(DateTime.LocalNow() as datetime),-60)

It is a SQL-style (PL/SQL in your case) and could be used only in data load step as a part of PL/SQL query statement to extract data from data source

TO_DATE ('14-12-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

It's pretty clear for me, if you are trying to use PowerQuery statement in PL/SQL query it won't work

[rjs2]

hey, in the PL/SQL example, it doesnt look like there is a calculation for date, that its hard coded in.  Are you saying in a data load (instead of direct query) I can no use a date calculation and I am basically beating my head against a brickwall for nothing

[rjs2]

I now understand what you are saying @az38 

What would be the correct way to only extract dates that are 60 days old?

[az38]

@rjs2 

the best option (from my point of view) is to filter it in SQL-query

the second option is to use filter "In Previous" 60 days in Power Query

and the third option is to use advanced filter in visual filters Pane in the Report mode

[rjs2]

I dont see a way to filter for anything 60 days or older.  there isnt a filter for not in the previous 60 days.

There has to be away to only pull and load data that is 60 days old based off a date field.

[rjs2]

I would rather have my query only pull the data needed, instead of pulling everything then filtering.  I am trying to be sensitive to our environment and make it less of a load on the database.

[rjs2]

I am doing both in Advanced Editor.  The one with TO_DATE works in Advanced Editor, but the Date.AddDays is throwing the error.

Why would I be getting an error with this, whats the correct syntax?  The error I am getting is suggesting I have an extra comma or a parenthesis is missing, but I am not.   

 Date.AddDays(Date.From(DateTime.LocalNow() as datetime),-60)