The M language

Power Query M, a language commonly referred to as “M,” is a powerful data transformation and data preparation language designed specifically for Microsoft’s Power Query, an ETL (Extract, Transform, Load) tool integrated into the Power BI, Excel, and other Microsoft products.

M was developed with the purpose of allowing users to clean, reshape, and combine data from various sources, facilitating the process of transforming raw data into the desired shape. By using the power of M, users can create custom functions, conditional columns, and advanced data transformations that go beyond what traditional spreadsheet applications can do.

Updated Articles

On the ‘Updated Articles’ page you can find a range of articles updated with original examples, distinct from the Microsoft documentation. Click below for an overview of these articles.

Functions

The Power Query M language has different types of functions. Learn about each by using the sidebar or by choosing a category below:

Accessing Data Functions

Accessing data functions connect to data sources and return table values.

Error Handling Functions

Error handling functions manage errors and enable error messages.

Replacer Functions

Replacer functions are used by other functions for replacing values.

Binary Functions

Binary functions help encode, compress and decompress your data.

Expression Functions

Expression functions help evaluate and compose M expressions.

Splitter Functions

Splitter functions help break data into smaller parts using specific criteria.

Combiner Functions

Combiner functions help merging values, following a pattern or condition.

Function Values

Function values help creating and invoking other M functions.

Table Functions

Table functions help creating, querying, and manipulating table values.

Comparer Functions

Comparer functions compare values and determine sorting within datasets.

Lines Functions

Table functions help creating, querying, and manipulating table values.

Text Functions

Text functions help in creating, combining and manipulating text values.

Date Functions

Date functions extract components from and manipulate date values.

List Functions

List functions help create, manipulate and work with list values.

Time Functions

Time functions help in creating and manipulating time values.

DateTime Functions

DateTime functions extract and manipulate DateTime values.

Logical Functions

Logical functions create and manipulate boolean (true/false) values.

Type Functions

Type functions help in creating and manipulating type values.

DateTimeZone Functions

DateTimeZone functions extract and manipulate DateTimeZone values.

Number Functions

Number functions round numbers, perform math and compare values.

Uri Functions

Uri functions help create, manipulating and parse URIs and query strings.

Duration Functions

Duration functions help create and manipulate duration values.

Record Functions

Record functions support creating, accessing, and altering records.

Value Functions

Value functions are used to evaluate and perform operations on values.

Enumerations

The M language provides a range of enumerations. These simplify the process of selecting options within a function. For example, you have the option to provide the Date.StartOfWeek function with the first day of the week by writing Day.Monday. Yet to be more concise you could enter the short-hand value 1. The Enumeration Overview delves into all enumerations and their short-hand alternatives.

Constants

Constant values are values that do not change. The M language supports several and they provide an easy way to input values that are immutable. More on these in the Constants Overview.

Value Types

A type value is a value that classifies other values. The M Language provides an extensive list of type values to classify values into data types. Explore all in the Types Overview.

Dynamic Values

A dynamic value is a value that takes into account your personal settings, resulting in potentially different values for each user. The M Language offers a limited selection of dynamic values to cater to varying user environments. Find all in the Dynamic Values Overview.

About this Reference

This Power Query reference contains comprehensive information on the M language. The Guide is updated regularly to reflect the latest changes in Microsoft products. Each M function is presented with a syntax, supporting articles and often with examples.

While this Power Query Guide is a BI Gorilla project created and maintained by Rick de Groot, it also incorporates official Microsoft documentation to provide you with the most accurate and up-to-date information.