Query Editor - Custom Column date formula

[Lifan]

Hi, Need help please. In the Query Editor, I just want to create a custom column using date formula. From my research, I'm supposed to use M Functions to compose it. And the #Date is function I'm trying to use but receiving error. if [FIRST_ELIGIBLE_DATE] >= #Date(2017,1,1) then YES else No Could anyone advise which part went wrong? Thanks!

[MarcelBeug · Accepted solution]

So you are in the Query Editor and you want to add a column with "YES" if FIRST_ELIGIBLE_DATE is on or afetr January 1, 2017 and "No" if it is not.

You can either add a conditional column and fill out the fields or add a custom column and enter the formula.

In either case, the generated code in the advanced editor will look like:

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "2017OrLater", each if [FIRST_ELIGIBLE_DATE] >= #date(2017,1,1) then "YES" else "No")

[MarcelBeug · Accepted solution]

So you are in the Query Editor and you want to add a column with "YES" if FIRST_ELIGIBLE_DATE is on or afetr January 1, 2017 and "No" if it is not.

You can either add a conditional column and fill out the fields or add a custom column and enter the formula.

In either case, the generated code in the advanced editor will look like:

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "2017OrLater", each if [FIRST_ELIGIBLE_DATE] >= #date(2017,1,1) then "YES" else "No")

[Lifan · Accepted solution]

Thank you MarcelBeug, it works now. It was the case sensitive got me, #Date should be #date.