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:
Expression | Equals |
---|---|
1 + 2 | Numeric addition: 3 |
#time(12,23,0) + #duration(0,0,2,0) | Time arithmetic: #time(12,25,0) |
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 ] |
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 |