Power Query M Language Foundations

Introduction

Power Query is often used through a graphical interface, but every transformation created in the Power Query Editor is represented internally by M, the functional language of Power Query. Understanding M enables analysts to read generated code, diagnose errors, parameterize transformations, create reusable functions, and implement logic that is difficult to express through menus alone.

M is a case-sensitive, expression-oriented language. An expression evaluates to a value, and values may include primitive data, lists, records, tables, functions, and errors. Most queries use a let ... in structure: the let section defines named expressions, while the in section identifies the value returned by the query.

This chapter introduces the foundations needed to interpret and edit common M expressions in Power BI and Excel Power Query.

Learning Objectives

By the end of this chapter, you should be able to:

• Explain the role of M in the Power Query engine.
• Read and edit let ... in expressions.
• Distinguish common M values and data types.
• Use operators, lists, records, and tables appropriately.
• Explain functions, parameters, and evaluation behaviour.
• Apply basic error-handling constructs.
• Assess maintainability, privacy, and governance implications of M code.

1. The M Evaluation Model

M is primarily a functional language. Rather than issuing a sequence of commands that alter a shared memory state, an M expression describes how a result is calculated. Names normally refer to values, and transformations return new values rather than modifying an existing value in place.

A typical query is:

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    Headers = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Headers, {{"Amount", type number}})
in
    Typed

The names Source, SalesSheet, Headers, and Typed are called step names or identifiers. The final line after in returns the value of Typed.

M uses a semicolon-like conceptual separation between steps, but the actual separator is a comma. A step can refer to an earlier step, not normally to a later one. This creates a readable dependency chain.

1.1 let ... in Structure

The general form is:

let
    name1 = expression1,
    name2 = expression2,
    name3 = expression3
in
    name3

The expressions are separated by commas. The final expression after in may be a name or any valid expression. For example:

let
    Revenue = 125000,
    Cost = 85000,
    Profit = Revenue - Cost
in
    Profit

The result is 40000.

Although the steps appear sequentially, M uses lazy evaluation in many circumstances: an expression is evaluated when its value is required. This does not mean that dependencies are ignored. If the returned result depends on Profit, then Revenue and Cost must be evaluated. Lazy evaluation can improve efficiency, but users should not assume that every step is evaluated exactly once or in a particular physical order.

1.2 Identifiers and Quoted Names

Simple names may contain letters, numbers, underscores, and other permitted characters, subject to identifier rules. Names containing spaces or special characters are enclosed in #"...".

let
    #"Filtered Rows" = Table.SelectRows(Source, each [Status] = "Open")
in
    #"Filtered Rows"

Step names should be meaningful and stable. Renaming a step requires updating every later reference to that step.

2. Values and Types

An M value is the result of evaluating an expression. Common values include:

M distinguishes values from types. The expression 42 is a value; type number is a type value. Type information is especially important in tables because Power Query must interpret text, dates, numbers, and logical values correctly.

2.1 Primitive Types

Numbers in M may represent integers, decimals, currency values, or percentages. Text is enclosed in double quotation marks. A double quotation mark inside text is represented by two quotation marks:

"The customer said ""approved"""

Logical values are lowercase:

[Amount] > 1000 and [Status] = "Approved"

Dates and related types use constructor functions:

#date(2025, 4, 1)
#datetime(2025, 4, 1, 9, 30, 0)
#duration(0, 2, 30, 0)

null is not the same as zero, an empty string, or false. It indicates the absence of a value. Comparisons and transformations involving null therefore require deliberate handling.

2.2 Type Declarations and Conversion

A type annotation can communicate the intended type of a value or column. Common conversion functions include:

Number.From("125.50")
Text.From(125.5)
Date.From("2025-04-01")
Logical.From("true")

For table columns, use functions such as:

Table.TransformColumnTypes(
    Source,
    {{"OrderDate", type date}, {"Quantity", Int64.Type}, {"Revenue", type number}}
)

Conversion may fail if the source contains incompatible text, invalid dates, or locale-specific formats. A robust solution validates assumptions about source data rather than silently forcing every value into a type.

3. Operators and Expressions

Operators combine or compare values.

Parentheses should be used when they make precedence clear:

([Revenue] - [Cost]) / [Revenue]

This expression calculates a margin ratio, provided that Revenue is not zero. In production code, the zero case should be handled explicitly.

Conditional expressions use if ... then ... else:

if [Revenue] = null then "Missing"
else if [Revenue] >= 10000 then "High"
else "Standard"

Because missing values can behave differently from ordinary values, it is often preferable to test for null explicitly using Value.Is or a controlled error-handling expression when the context requires it.

4. Lists

A list is an ordered sequence enclosed in braces:

{10, 20, 30, 40}

List positions are zero-based. Therefore:

{10, 20, 30}{0}

returns 10, while {10, 20, 30}{2} returns 30.

Useful list functions include:

List.Count({10, 20, 30})
List.Sum({10, 20, 30})
List.Max({10, 20, 30})
List.Transform({1, 2, 3}, each _ * 10)
List.Select({1, 2, 3, 4}, each _ > 2)

The underscore _ represents the current item in an each expression. Thus, each _ 10 is a concise function equivalent to (x) => x 10.

Lists can be generated using ranges:

{1..5}

This produces {1, 2, 3, 4, 5}. A list can also contain records, text, dates, or other lists. In data preparation, lists are useful for lookup values, parameter collections, dynamic column names, and intermediate results.

5. Records

A record is a collection of named fields enclosed in square brackets:

[Product="Laptop", Quantity=3, UnitPrice=850]

Fields can be accessed using bracket notation:

Order[Quantity]

A record can be expanded or modified with functions such as:

Record.Field(Order, "Quantity")
Record.AddField(Order, "Revenue", Order[Quantity] * Order[UnitPrice])
Record.FieldNames(Order)

Records are central to row-level operations. In a table transformation such as:

Table.AddColumn(
    Sales,
    "Revenue",
    each [Quantity] * [UnitPrice],
    type number
)

each receives the current row as a record, and [Quantity] accesses a field in that record.

6. Tables and Structured Data

A table is a structured value consisting of rows and columns. Conceptually, each row is a record and the complete table is a collection of records with a defined schema.

A small table can be constructed directly:

#table(
    {"Product", "Quantity", "Price"},
    {{"A", 2, 10.5}, {"B", 3, 8.0}}
)

Common table operations include:

Table.SelectRows(Source, each [Quantity] > 0)
Table.SelectColumns(Source, {"Product", "Quantity"})
Table.RenameColumns(Source, {{"Qty", "Quantity"}})
Table.AddColumn(Source, "Value", each [Quantity] * [Price], type number)
Table.Sort(Source, {{"Value", Order.Descending}})
Table.Group(Source, {"Product"}, {{"Total", each List.Sum([Value]), type number}})

The expression [Value] inside a row function refers to the field in the current row. In a grouping aggregation, [Value] refers to the grouped column values available to the aggregation function.

Table transformations are usually immutable: a function returns a new table rather than changing the source table. This supports a clear transformation pipeline and makes dependencies easier to audit.

6.1 Table Schema and Metadata

A table’s schema includes column names and types. Type information affects sorting, filtering, aggregation, and relationships in the data model. For example, sorting text values "100", "20", and "3" differs from sorting numerical values 100, 20, and 3.

Analysts should set types early enough to support valid transformations, but should also consider whether source data is complete and reliable before conversion. A type step is not a substitute for data-quality investigation.

7. Functions and Parameters

A function maps input values to an output value. Its general form is:

(parameter1, parameter2) => expression

For example:

AddTax = (Amount as number, Rate as number) as number => Amount * (1 + Rate)

The function can be called as follows:

AddTax(100, 0.2)

which returns 120.

A function can be applied to a table column:

Table.AddColumn(Sales, "GrossAmount", each AddTax([NetAmount], 0.2), type number)

Function parameters and return types are optional but valuable. Type declarations document intent and can expose invalid inputs earlier.

M includes built-in functions in namespaces such as Table, List, Record, Text, Date, Number, and Value. The function signature matters: users should check the expected argument order, optional parameters, and return type rather than relying on a guessed pattern.

8. Errors and Error Handling

An M error is a value representing a failure during evaluation. Typical causes include division by zero, invalid type conversion, missing fields, unavailable files, and out-of-range list positions.

The try expression captures either a successful result or an error record:

try Number.From([AmountText])

A successful result has fields such as HasError = false and Value; a failed result has HasError = true and Error. A practical pattern is:

Table.AddColumn(
    Source,
    "ParsedAmount",
    each try Number.From([AmountText]) otherwise null,
    type number
)

The otherwise clause supplies a replacement value when the attempted expression fails. This is useful when invalid records should be retained for later review. However, replacing every error with null can conceal serious problems. A governance-conscious design may preserve an error-status column:

Table.AddColumn(
    Source,
    "AmountResult",
    each try Number.From([AmountText])
)

The resulting record can then be expanded or inspected to distinguish valid and invalid rows.

error can explicitly raise an error:

if [Quantity] < 0 then error "Quantity cannot be negative"
else [Quantity]

Explicit errors are appropriate when continuing would produce misleading data. Error handling should therefore balance operational resilience with transparency.

9. Worked Example: Building a Reliable Sales Transformation

Assume a source table contains OrderID, OrderDateText, QuantityText, UnitPriceText, and Status. The objective is to create a clean table with typed fields and calculated revenue.

let
    Source = #table(
        {"OrderID", "OrderDateText", "QuantityText", "UnitPriceText", "Status"},
        {
            {"O-100", "2025-01-05", "2", "125.50", "Approved"},
            {"O-101", "2025-01-06", "bad", "80.00", "Approved"},
            {"O-102", "2025-01-07", "3", "50.00", "Cancelled"}
        }
    ),
    Parsed = Table.AddColumn(
        Source,
        "Parsed",
        each [
            OrderDate = try Date.From([OrderDateText]),
            Quantity = try Int64.From([QuantityText]),
            UnitPrice = try Number.From([UnitPriceText])
        ]
    ),
    Expanded = Table.ExpandRecordColumn(
        Parsed,
        "Parsed",
        {"OrderDate", "Quantity", "UnitPrice"},
        {"OrderDate", "Quantity", "UnitPrice"}
    ),
    Valid = Table.SelectRows(
        Expanded,
        each [Quantity][HasError] = false and [UnitPrice][HasError] = false
    ),
    Values = Table.TransformColumns(
        Valid,
        {
            {"Quantity", each [Value], Int64.Type},
            {"UnitPrice", each [Value], type number},
            {"OrderDate", each [Value], type date}
        }
    ),
    Approved = Table.SelectRows(Values, each [Status] = "Approved"),
    WithRevenue = Table.AddColumn(
        Approved,
        "Revenue",
        each [Quantity] * [UnitPrice],
        type number
    )
in
    WithRevenue

Conceptually, the query separates parsing, validation, filtering, and calculation. The invalid quantity in order O-101 is excluded by the validation step, while the cancelled order is excluded by the business rule. The remaining approved order has revenue of 2 × 125.50 = 251.00.

In a production solution, the error records should generally be written to a separate quality-report query rather than discarded without explanation.

10. Applied Case: Regional Sales Data Integration

Background

An illustrative retail organisation receives monthly sales files from four regions. Each file was prepared by a different team. Dates use inconsistent formats, some quantity fields contain text such as "N/A", and regional files use different column names.

Problem

The organisation needs a refreshable Power BI dataset with consistent columns, transparent data-quality reporting, and a standard revenue calculation. Manual correction is slow and cannot be reliably repeated each month.

Analysis

The underlying issue is not merely missing data. It is a combination of schema variation, type inconsistency, and unclear error treatment. A transformation that simply removes invalid rows would produce totals without showing how much data was excluded.

Approach and Solution

A reusable function is created to:

1. Rename regional columns to a standard schema.
2. Parse dates and numeric fields with try.
3. Add Boolean validity indicators.
4. Preserve source-file and region identifiers.
5. Calculate revenue only for valid records.
6. Output rejected records to a separate quality table.

The main query combines the standardized outputs using Table.Combine. A data-quality query groups rejected rows by region and error type. Managers can therefore distinguish a genuine fall in sales from a deterioration in source-file quality.

Outcome

The process becomes repeatable, refreshable, and auditable. Business users receive a consistent sales table, while data stewards receive a targeted exception report. The use of a function also reduces duplicated logic across regional queries.

Learning Points

• M functions support reuse and standardisation.
• Error handling should expose, not conceal, data-quality problems.
• Type conversion is a business-control issue as well as a technical issue.
• Source provenance should be retained when combining files.

11. Best Practices, Governance, and Limitations

Readability and maintenance

Use descriptive step names, keep transformations logically grouped, and avoid excessively long expressions. Comments can explain unusual business rules:

// Exclude cancelled orders from recognised revenue

Use the Advanced Editor to inspect the complete query, but make small changes and refresh frequently. A change that is syntactically valid may still be logically incorrect.

Privacy and source governance

Power Query may combine information from multiple sources. Privacy-level settings and organisational policies can affect how sources are combined and may prevent unsafe data leakage. Credentials, file paths, and connection details should not be hard-coded into shared code when parameters or managed connections are available.

Query folding

For some relational sources, Power Query can translate transformations into source-native operations. This is called query folding. Folding can improve performance, but it depends on the connector, source system, transformation, and sequence of operations. Analysts should not assume that every M function folds. Filtering early, selecting only required columns, and checking native query diagnostics where available are sensible practices.

Limits of M

M is highly effective for extraction and transformation, but it is not a replacement for a governed data warehouse, a semantic model, or a programming language designed for large-scale distributed computation. Very complex logic may be better implemented upstream, especially when it must be shared across many reports or controlled centrally.

Contemporary Relevance

Modern data preparation increasingly involves cloud storage, APIs, dataflows, and automated quality checks. M remains important because it provides a portable transformation layer across many Power Query-enabled products. AI-assisted code generation can suggest M expressions, but generated code must be reviewed for privacy, type correctness, query-folding behaviour, error handling, and business-rule accuracy. An apparently plausible expression is not evidence that the transformation is valid.

Chapter Summary

M is the expression language behind Power Query. Its central structure is let ... in, where named expressions form a dependency chain and the in expression returns the query result. M values include primitive values, lists, records, tables, functions, null, and errors. Types determine how values are interpreted and how table operations behave.

Operators support arithmetic, comparison, logical tests, concatenation, and collection access. Lists provide ordered collections, records provide named fields, and tables provide structured rows and columns. Functions allow reusable and parameterized logic, while each provides concise anonymous functions for row and list operations.

The try ... otherwise pattern can make refreshes resilient, but indiscriminate error replacement can hide important data-quality problems. Professional Power Query solutions therefore combine type discipline, clear naming, validation, provenance, privacy awareness, and performance considerations such as query folding.

Key Terms

M: The functional expression language used by Power Query.

Expression: A construct that evaluates to a value.

let ... in: The structure used to define named expressions and return a final result.

Identifier: A name used to refer to a value or step.

Value: The result produced by evaluating an expression.

Type: A description of the kind of value, such as number, text, date, or table.

Null: A value representing absence or missingness.

List: An ordered collection of values.

Record: A collection of named fields.

Table: A structured collection of rows and columns.

Function: A value that accepts arguments and returns a result.

Parameter: An input supplied to a function or query.

each: Shorthand syntax for an anonymous function.

Schema: The names and types that describe a table.

try: An expression that captures success or failure during evaluation.

Error value: A value representing a failed computation.

Query folding: Translation of eligible Power Query operations to the source system.

Lazy evaluation: Evaluation of an expression when its result is required.

Data lineage: The ability to trace data from its source through transformations.

Review Questions

Short Answer Questions

1. What is the purpose of the in expression in a let ... in query?
2. How does a record differ from a list in M?
3. Why is null different from zero?
4. What does the underscore represent in an each expression?
5. State one benefit and one risk of using try ... otherwise.

Long Answer Questions

1. Explain the M evaluation model and illustrate it with a three-step let ... in expression.
2. Compare lists, records, and tables, including one practical Power Query use for each.
3. Explain why data types are important when transforming and modelling Power BI data.
4. Describe how functions and parameters can reduce duplication in a multi-file data-integration process.
5. Discuss best practices for balancing error resilience, transparency, and governance in M queries.

Analytical / Application Questions

1. A sales table contains numeric values stored as text, invalid entries, and cancelled transactions. Design a sequence of M transformations that parses, validates, filters, and calculates revenue while preserving a quality-report output.
2. A query refresh becomes slow after several table transformations against a SQL source. Analyse how query folding, column selection, filtering order, and upstream processing might affect performance.
3. A generated M expression replaces all conversion errors with zero. Evaluate the risks of this design and propose a more defensible alternative for management reporting.

Multiple Choice Questions with Answers

Question 1

Which part of a let ... in expression identifies the value returned by the query?

A. The first step in the let section

B. The final expression after in

C. The first identifier in the query

D. The last comma in the query

Correct Answer: B. The final expression after in

Question 2

Which M value represents an ordered collection?

A. Record

B. Table

C. List

D. Type

Correct Answer: C. List

Question 3

What does {10, 20, 30}{0} return in M?

A. 10

B. 20

C. 30

D. An error because indexing begins at one

Correct Answer: A. 10

Question 4

Which notation accesses the Quantity field of a current row record?

A. {Quantity}

B. (Quantity)

C. [Quantity]

D. <Quantity>

Correct Answer: C. [Quantity]

Question 5

What is the main purpose of try ... otherwise?

A. To sort a table

B. To capture a failure and provide an alternative result

C. To convert every value to text

D. To force query folding

Correct Answer: B. To capture a failure and provide an alternative result

Question 6

What does the expression (x as number) as number => x * 2 define?

A. A table schema

B. A list range

C. A function

D. A record field

Correct Answer: C. A function

Question 7

Which function is most directly suited to adding a calculated column to a table?

A. Table.AddColumn

B. List.Count

C. Record.FieldNames

D. Text.Combine

Correct Answer: A. Table.AddColumn

Question 8

What is a principal danger of replacing all errors with null without tracking them?

A. The query becomes case-sensitive

B. Data-quality problems may be concealed

C. Lists become one-based

D. Dates automatically become numbers

Correct Answer: B. Data-quality problems may be concealed

Question 9

What does query folding mean?

A. Compressing an M script into fewer lines

B. Translating eligible transformations to the source system

C. Converting records into lists

D. Encrypting source credentials

Correct Answer: B. Translating eligible transformations to the source system

Question 10

In the expression AddTax(100, 0.2), what result is produced by AddTax = (Amount as number, Rate as number) as number => Amount * (1 + Rate)?

A. 20

B. 100.20

C. 120

D. 200

Correct Answer: C. 120