Share via


Operators

The Power Query M formula language includes a set of operators that can be used in an expression. Operators are applied to operands to form symbolic expressions. For example, in the expression 1 + 2 the numbers 1 and 2 are operands and the operator is the addition operator (+).

The meaning of an operator can vary depending on the type of operand values. The language has the following operators:

Plus operator (+)

Expression Equals
1 + 2 Numeric addition: 3
#time(12,23,0) + #duration(0,0,2,0) Time arithmetic: #time(12,25,0)

Combination operator (&)

Function Equals
"A" & "BC" Text concatenation: "ABC"
{1} & {2, 3} List concatenation: {1, 2, 3}
[ a = 1 ] & [ b = 2 ] Record merge: [ a = 1, b = 2 ]

List of M operators

Common operators which apply to null, logical, number, time, date, datetime, datetimezone, duration, text, binary)

Operator Description
> Greater than
>= Greater than or equal
< Less than
<= Less than or equal
= Equal
<> Not equal

Logical operators (In addition to Common operators)

Operator Description
or Conditional logical OR
and Conditional logical AND
not Logical NOT

Number operators (In addition to Common operators)

Operator Description
+ Sum
- Difference
* Product
/ Quotient
+x Unary plus
-x Negation

Text operators (In addition to Common operators)

Operator Description
& Concatenation

List, record, table operators

Operator Description
= Equal
<> Not equal
& Concatenation

Record lookup operator

Operator Description
[] Access the fields of a record by name.

List indexer operator

Operator Description
{} Access an item in a list by its zero-based numeric index.

Type compatibility and assertion operators

Operator Description
is The expression x is y returns true if the type of x is compatible with y, and returns false if the type of x is not compatible with y.
as The expression x as y asserts that the value x is compatible with y as per the is operator.

Date operators

Operator Left Operand Right Operand Meaning
x + y time duration Date offset by duration
x + y duration time Date offset by duration
x - y time duration Date offset by negated duration
x - y time time Duration between dates
x & y date time Merged datetime

Datetime operators

Operator Left Operand Right Operand Meaning
x + y datetime duration Datetime offset by duration
x + y duration datetime Datetime offset by duration
x - y datetime duration Datetime offset by negated duration
x - y datetime datetime Duration between datetimes

Datetimezone operators

Operator Left Operand Right Operand Meaning
x + y datetimezone duration Datetimezone offset by duration
x + y duration datetimezone Datetimezone offset by duration
x - y datetimezone duration Datetimezone offset by negated duration
x - y datetimezone datetimezone Duration between datetimezones

Duration operators

Operator Left Operand Right Operand Meaning
x + y datetime duration Datetime offset by duration
x + y duration datetime Datetime offset by duration
x + y duration duration Sum of durations
x - y datetime duration Datetime offset by negated duration
x - y datetime datetime Duration between datetimes
x - y duration duration Difference of durations
x * y duration number N times a duration
x * y number duration N times a duration
x / y duration number Fraction of a duration

Note

Not all combinations of values may be supported by an operator. Expressions that, when evaluated, encounter undefined operator conditions evaluate to errors. For more information about errors in M, go to Errors

Error example:

Function Equals
1 + "2" Error: adding number and text is not supported