Operators

Updated on

Operators act as the building blocks of expressions, serving as the “verbs” that dictate what action to take on operands, which are the “nouns” or the data points being manipulated.

Table of contents

Take the expression 1 + 2: here, 1 and 2 are the operands, and the plus sign (+) is the operator that instructs the language to add them together. Understanding this interplay between operators and operands is important to create effective and accurate expressions.

Types of Operators

In the Power Query M language, you’ll find a wide variety of operators to use in your expressions. But it’s important to know that not all operators work with every type of value. Some only work with their own type, while others are more flexible. This section gives you all the details you need to understand how these operators interact with different types of data.

Although there is a wide selection of operators, not all operators are compatible with each other. The following overview explains. For future reference on operators, feel free to download the cheatsheet.

Operators and compatibility in Power Query M

Comparison Operators

Comparison operators evaluate the relationship between two values. They are used for actions like removing rows that don’t meet a certain condition. But also for conditional statements that apply different transformations based on a condition.

Applies to: null, logical, number, time, date, datetime, datetimezone, duration, text, binary

OperationOperatorDescription
Equal to=Checks if two values are equal.
Not equal to<>Checks if two values are not equal.
Greater than>Checks if one value is greater than another.
Less than<Checks if one value is less than another.
Greater than or equal to>=Checks if one value is greater than or equal to another.
Less than or equal to<=Checks if one value is less than or equal to another.

Arithmetic Operators

Arithmetic operators perform basic mathematical operations on numbers. They are often used when creating an expression. You see them in transformations like calculating percentages, converting currencies or subtracting values.

OperationOperatorDescription
Addition+Adds two numbers together.
SubtractionSubtracts one number from another.
Multiplication*Multiplies two numbers.
Division/Divides one number by another.
Unary plus+xExplicitly indicates a number is positive.
Negation-xReverses the sign of a number.

Looking at some of the arithmetic operators, there are a few special cases:

  • The value of type date, datetime and datetimezone all support subtraction of addition of duration values.
  • Duration values can be used for adding or subtracting values from date, datetime and datetimezone. But they also support division or multiplication with a number value.
  • Unary plus or negation of values can be performed on duration or number values. You can also apply these on null values without an error, but the outcome will be null.

Logical Operators

Logical operators evaluate the truth or falsehood of a statement. You see them commonly used to test multiple conditions. For example, it can test if a row meets multiple criteria before including it in a dataset.

OperationOperatorDescription
Logical AndandReturns true if both conditions are true.
Logical OrorReturns true if at least one condition is true.
Logical NotnotReverses the truth value of a condition.

Concatenation Operator

Concatenation operators combine two values. They are generally used to combine multiple strings into a single one, but can also be used to combine other types of values.

OperationOperatorDescription
Concatenation&Joins two or more strings together.

Looking at some of the concatenation operators there are a few special cases:

  • The operator can combine a date value with a time value to form a datetime value.
  • Combining record values results in overwriting fields when duplicate field names exist.
  • Combining list or table values results in combining them into a single structured value.

List, Record, and Table Operators

A selection of operators work with values of the type List, Record and Table.

OperationOperatorDescription
Equal=Check if two lists, tables, or records are different in content.
Not Equal<>Checks if two lists, tables, or records are different in content.
Concatenation&Combines two lists, tables, or records into a single structure.
Field Selection[ x ]Access the fields of a record or table by name.
Field Projection[ [ x ], [ y ] ]Project two fields of a record or table by name.
Item Selection{ x }Access an item in a list by its zero-based numeric index.

Other Operators

OperationOperatorDescription
Coalesce??Returns the first non-null value between two operands.
MetadatametaAssociate metadata with a value.

Compatibility

Type Compatibility and Assertion

OperationOperatorDescription
Type compatibilityisTests if the type of x is compatible with y.
Type AssertionasAsserts that the type of x is compatible with y.

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 OperandDescription
x + ydatetimedurationDateTime offset by duration
x + ydurationDateTimeDateTime offset by duration
x – yDateTimedurationDateTime offset by duration
x – yDateTimeDateTimeDuration between two datetimes

DateTimeZone Operators

OperatorLeft OperandRight OperandDescription
x + yDateTimeZonedurationDatetimezone offset by duration
x + ydurationDateTimeZoneDatetimezone offset by duration
x – yDateTimeZonedurationDatetimezone offset by duration
x – yDateTimeZoneDateTimeZoneDuration between two DateTimeZones

Duration Operators

OperatorLeft OperandRight OperandDescription
x + yDateTimedurationDateTime offset by duration
x + ydurationDateTimeDateTime offset by duration
x + ydurationdurationSum of duration
x – yDateTimedurationDateTime offset by negated duration
x – yDateTimeDateTimeDuration between two datetimes
x – ydurationdurationDifference between durations
x * ydurationnumberDuration multiplied by a number
x * ynumberdurationDuration multiplied by a number
x / ydurationnumberDuration divided by a number

Operator Precedence

Operators are applied in a specific order called the ‘operator precedence’. You can find the order in which operators are applied below.

CategoryExpressionDescription
Primary Operatorsi
@i
Identifier expression
(x)Parenthesized expression
x[ i ]Element retrieval from a record or table
x{ y }Item access from a list
x(…)Function invocation
{x, y, …}Identity indicates the value is positive
[ i = x, … ]Record initialization / creation
Not implemented
Unary+xIdentity, indicates the value is positive
-xNegation, inverts the sign
not xLogical negation, reverse the truth value
Metadatax meta yAssociate metadata
Multiplicativex * yMultiplication
x / yDivision
Additivex + yAddition
x – ySubtraction
Relationalx < yLess than
x > yGreater than
x <= yLess than or equal
x >= yGreater than or equal
Equalityx = yEqual
x <> yNot equal
Type assertionx as yIs compatible nullable-primitive type or error
Type conformancex is yTest if compatible nullable-primitive type
Logical ANDx and yShort-circuiting conjunction
Logical ORx or yShort-circuiting disjunction
Coalescex ?? yNull coalescing operator
BI Gorilla Blog

Contribute » | Contributors: Rick de Groot, Brian Julius
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/operators