Using the expression editor for add column transformations

On user input, auto-completion should be able to suggest:

  • Operators (such as +, =, IS NULL)
  • Functions (such as ABS, IFNULL, SUBSTRING)
  • Operands
    • logical column names that were output from the previous step, enclosed in square brackets (ex. [CategoryID])
    • values like 1, true, 'string', 0.5, #12/31/2017#, #12/31/2017 11:59:59 PM#

These functions and operators should be supported:

  • Operators
    • = equals
    • * times
    • + plus
    • / divided by
    • - minus
    • % modulo
    • < less than
    • > greater than
    • <> not equal to
    • <= less than or equal to
    • >= greater than or equal to
    • AND
    • OR
    • LIKE
    • NOT LIKE
    • IS NULL
    • IS NOT NULL
  • Functions
    • ABS
    • ADD
    • ADDPARENTCHILD
    • ARCCOS
    • ARCSIN
    • ARCTAN
    • ATAN2
    • Casting: FLOAT(), INTEGER(), DATETIME()
    • CEILING
    • COS
    • DATEADD
    • DATEDIFF
    • DATEPART
    • DATETIMEPARSE
    • DEGREES
    • EXP
    • FLOOR
    • FORMAT
    • GETDAYID
    • GETMONTHID
    • GETWEEKID
    • IFNULL
    • IIF
    • ISINF
    • ISNAN
    • LENGTH
    • LN
    • LOG
    • LOG10
    • LTRIM
    • NOW
    • NOWDATE
    • POSITION
    • POW
    • RADIANS
    • PI
    • RANDOM
    • RTRIM
    • SIGN
    • SIN
    • SPACE
    • SQRT
    • SUBSTRING
    • TAN
    • TOLOWER
    • TOTIME
    • TOUPPER
    • TRIM
  • Functions that are NOT included:
    • ADDPARENTCHILD
    • DISPLAY BY
    • DISPLAY WHERE
    • DRANK
    • DTOP
    • FIND
    • FUNCTION
    • FUNTIONLOOKUP
    • GETLEVELATTRIBUTE
    • GETLEVELVALUE
    • GETPROMPTFILTER
    • GETPROMPTVALUE
    • GETVARIABLE
    • LET
    • LOOKUPROW
    • LOOKUPVALUE
    • MEDIAN
    • NEXTCHILD
    • NUMROWS
    • ORDER BY
    • PTILE
    • RANK
    • REMOVEALL
    • REMOVEAT
    • REXP
    • ROWNUMBER
    • RSUM
    • SPARSE
    • STAT
    • TOP
    • TRANSFORM
    • TREND