Forum Discussion

Lifan's avatar
Lifan
Frequent Visitor
9 years ago
Solved

Query Editor - Custom Column date formula

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!
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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's avatar
    Lifan
    9 years ago

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

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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's avatar
      Lifan
      Frequent Visitor
      9 years ago

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