Operators in the Power Query M Language

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

Types of Operators
Compatibility
Operator Precedence

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

Operation	Operator	Description
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.

Operation	Operator	Description
Addition	+	Adds two numbers together.
Subtraction	–	Subtracts one number from another.
Multiplication	*	Multiplies two numbers.
Division	/	Divides one number by another.
Unary plus	+x	Explicitly indicates a number is positive.
Negation	-x	Reverses 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.

Operation	Operator	Description
Logical And	and	Returns true if both conditions are true.
Logical Or	or	Returns true if at least one condition is true.
Logical Not	not	Reverses 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.

Operation	Operator	Description
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.

Operation	Operator	Description
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

Operation	Operator	Description
Coalesce	??	Returns the first non-null value between two operands.
Metadata	meta	Associate metadata with a value.

Compatibility

Type Compatibility and Assertion

Operation	Operator	Description
Type compatibility	is	Tests if the type of x is compatible with y.
Type Assertion	as	Asserts that the type of x is compatible with y.

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	Description
x + y	datetime	duration	DateTime offset by duration
x + y	duration	DateTime	DateTime offset by duration
x – y	DateTime	duration	DateTime offset by duration
x – y	DateTime	DateTime	Duration between two datetimes

DateTimeZone Operators

Operator	Left Operand	Right Operand	Description
x + y	DateTimeZone	duration	Datetimezone offset by duration
x + y	duration	DateTimeZone	Datetimezone offset by duration
x – y	DateTimeZone	duration	Datetimezone offset by duration
x – y	DateTimeZone	DateTimeZone	Duration between two DateTimeZones

Duration Operators

Operator	Left Operand	Right Operand	Description
x + y	DateTime	duration	DateTime offset by duration
x + y	duration	DateTime	DateTime offset by duration
x + y	duration	duration	Sum of duration
x – y	DateTime	duration	DateTime offset by negated duration
x – y	DateTime	DateTime	Duration between two datetimes
x – y	duration	duration	Difference between durations
x * y	duration	number	Duration multiplied by a number
x * y	number	duration	Duration multiplied by a number
x / y	duration	number	Duration 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.

Category	Expression	Description
Primary Operators	i @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	+x	Identity, indicates the value is positive
	-x	Negation, inverts the sign
	`not` x	Logical negation, reverse the truth value
Metadata	x `meta` y	Associate metadata
Multiplicative	x * y	Multiplication
Multiplicative	x / y	Division
Additive	x + y	Addition
Additive	x – y	Subtraction
Relational	x < y	Less than
	x > y	Greater than
	x <= y	Less than or equal
	x >= y	Greater than or equal
Equality	x = y	Equal
Equality	x <> y	Not equal
Type assertion	x `as` y	Is compatible nullable-primitive type or error
Type conformance	x `is` y	Test if compatible nullable-primitive type
Logical AND	x `and` y	Short-circuiting conjunction
Logical OR	x `or` y	Short-circuiting disjunction
Coalesce	x `??` y	Null coalescing operator

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