Table of contents
TOC
Collapse the table of content
Expand the table of content

Operators

Owen Duncan|Last Updated: 10/5/2018
|
1 Contributor

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 (+)

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

Combination operator (&)

FunctionEquals
"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)

OperatorDescription
>Greater than
>=Greater than or equal
<Less than
<=Less than or equal
=Equal
<>Not equal

Logical operators (In addition to Common operators)

OperatorDescription
orConditional logical OR
andConditional logical AND
notLogical NOT

Number operators (In addition to Common operators)

OperatorDescription
+Sum
-Difference
*Product
/Quotient
+xUnary plus
-xNegation

Text operators (In addition to Common operators)

OperatorDescription
&Concatenation

List, record, table operators

OperatorDescription
=Equal
<>Not equal
&Concatenation

Record lookup operator

OperatorDescription
[]Access the fields of a record by name.

List indexer operator

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

Type compatibility and assertion operators

OperatorDescription
isThe 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.
asThe expression x as y asserts that the value x is compatible with y as per the is operator.

Date operators

OperatorLeft OperandRight OperandMeaning
x + ytimedurationDate offset by duration
x + ydurationtimeDate offset by duration
x - ytimedurationDate offset by negated duration
x - ytimetimeDuration between dates
x & ydatetimeMerged datetime

Datetime operators

OperatorLeft OperandRight OperandMeaning
x + ydatetimedurationDatetime offset by duration
x + ydurationdatetimeDatetime offset by duration
x - ydatetimedurationDatetime offset by negated duration
x - ydatetimedatetimeDuration between datetimes

Datetimezone operators

OperatorLeft OperandRight OperandMeaning
x + ydatetimezonedurationDatetimezone offset by duration
x + ydurationdatetimezoneDatetimezone offset by duration
x - ydatetimezonedurationDatetimezone offset by negated duration
x - ydatetimezonedatetimezoneDuration between datetimezones

Duration operators

OperatorLeft OperandRight OperandMeaning
x + ydatetimedurationDatetime offset by duration
x + ydurationdatetimeDatetime offset by duration
x + ydurationdurationSum of durations
x - ydatetimedurationDatetime offset by negated duration
x - ydatetimedatetimeDuration between datetimes
x - ydurationdurationDifference of durations
x * ydurationnumberN times a duration
x * ynumberdurationN times a duration
x / ydurationnumberFraction 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, see Errors

Error example:

FunctionEquals
1 + "2"Error: adding number and text is not supported
© 2018 Microsoft