Power Query M Language Fundamentals

Introduction

Power Query is an extract, transform and load (ETL) engine used in Power BI, Excel and other Microsoft data products. Although many transformations can be created through the graphical interface, every query is ultimately represented by code written in the Power Query M language. Learning M enables a developer to create reusable transformations, diagnose errors, control query logic and build solutions that are difficult or impossible to produce solely through menu commands.

M is a functional, case-sensitive language. It evaluates expressions rather than executing a sequence of imperative statements. A query generally produces one value: that value may be a scalar such as a number, or a structured value such as a list, record, table or function. The language is designed for data shaping, so its type system and standard library contain extensive support for text, dates, lists, records and tables.

This chapter develops the foundations required to read and write M expressions in the Advanced Editor. The emphasis is on concepts that support professional Power BI development: expression structure, values and types, operators, structured data, functions, scope, library functions, error handling and maintainable code.

Learning Objectives

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

1. Explain the expression-oriented and case-sensitive nature of M.
2. Construct and interpret let ... in expressions.
3. Distinguish primitive values, lists, records, tables, functions and types.
4. Use M operators for arithmetic, comparison, logical tests, concatenation and access.
5. Apply built-in functions to text, lists, records, tables and dates.
6. Define custom functions and understand parameters and scope.
7. Edit queries safely in the Advanced Editor.
8. Diagnose common syntax, type, privacy and data-quality errors.
9. Apply professional practices for readable, reliable and governed M code.

1. The M Evaluation Model

In an imperative language, a program is often understood as a sequence of commands that change state. M is better understood as a language of expressions. An expression is evaluated and returns a value. Even a complete query is an expression.

For example:

1 + 2

returns the number 3. A more useful query might be:

Text.Upper("power query")

which returns "POWER QUERY".

M is case-sensitive. Text.Upper, text.upper and TEXT.UPPER are not interchangeable. Names, function names and many identifiers must therefore be written with their exact capitalization.

M also uses lazy evaluation in important situations: an expression is generally evaluated when its value is required. This can improve efficiency, but it does not remove the need to design efficient queries. Expensive source operations, repeated function calls and unnecessary column transformations can still reduce performance.

1.1 Comments

Single-line comments begin with //:

// Remove rows with no transaction identifier
FilteredRows = Table.SelectRows(Source, each [TransactionID] <> null)

Multiline comments are enclosed by / and /:

/*
This section standardizes product codes.
The source contains both numeric and text representations.
*/

Comments should explain purpose, assumptions or unusual decisions. They should not merely repeat what the code visibly does.

2. The let ... in Structure

Most practical Power Query queries use a let ... in expression. The general pattern is:

let
    Step1 = expression1,
    Step2 = expression2,
    Step3 = expression3
in
    Step3

Each name before an equals sign identifies a value. Steps are separated by commas. The expression after in is the final result returned by the query.

A simple example is:

let
    OriginalPrice = 120,
    DiscountRate = 0.15,
    DiscountAmount = OriginalPrice * DiscountRate,
    FinalPrice = OriginalPrice - DiscountAmount
in
    FinalPrice

The result is 102. FinalPrice can refer to earlier names, but an earlier step cannot refer to a later step. The names in a let expression form a scope.

The final step does not have to be the last defined step. For example:

let
    A = 10,
    B = 20,
    Total = A + B,
    UnusedValue = 999
in
    Total

The returned value is 30; UnusedValue is not returned. Unused steps should normally be removed because they create confusion and may cause unnecessary evaluation.

2.1 Identifiers and quoted identifiers

Simple names can contain letters, numbers and underscores subject to M naming rules. Names containing spaces or special characters use the quoted identifier form:

let
    #"Source Data" = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(#"Source Data", {{"Amount", type number}})
in
    #"Changed Type"

The #"..." notation is an escaped identifier, not a text value. A text value is enclosed in ordinary double quotation marks, such as "Sales".

3. Values and Types

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

M includes values and types as distinct concepts. type number is a type value; 42 is a number value. Type values can be used in declarations and type inspection.

The type of a value can be inspected using Value.Type:

Value.Type(42)

A value can be converted with functions such as Number.From, Text.From, Date.From and DateTime.From. Conversion is not always safe. For example, text that does not represent a valid number may cause an error when converted with Number.From.

3.1 Null is not the same as an empty string or zero

These values have different meanings:

• null: no value is present.
• "": a text value containing zero characters.
• 0: a numeric value equal to zero.

A test for null uses equality or inequality operators:

[Email] = null
[Email] <> null

For robust cleansing, it is often necessary to handle all three conditions separately.

3.2 Type declarations

Table schemas can be described with type table expressions. A simplified schema is:

type table [CustomerID = Int64.Type, Name = text, Revenue = type number]

In practice, Table.TransformColumnTypes is frequently used to apply types to columns:

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

Correct types matter because they affect sorting, aggregation, relationships, date intelligence and error detection in the Power BI model.

4. Operators

Operators combine or compare values.

4.1 Arithmetic operators

M supports +, -, * and / for appropriate numeric values. The & operator concatenates text and can also combine some structured values.

let
    Quantity = 4,
    UnitPrice = 25,
    Revenue = Quantity * UnitPrice,
    Tax = Revenue * 0.20,
    Total = Revenue + Tax
in
    Total

Division by zero produces an error. A defensive expression can test the denominator before dividing:

if [Units] = 0 then null else [Revenue] / [Units]

4.2 Comparison operators

The comparison operators are:

Comparisons should normally involve compatible types. Comparing a number with text can produce an error or an unexpected result depending on context.

4.3 Logical operators

The principal logical operators are and, or and not:

[Status] = "Open" and [Balance] > 0

[Region] = "East" or [Region] = "West"

not [IsCancelled]

Parentheses clarify complex conditions:

([Status] = "Open" or [Status] = "Pending") and [Balance] > 0

4.4 Concatenation and access operators

Text can be joined with &:

[FirstName] & " " & [LastName]

Record fields can be accessed with a field selector:

Customer[Name]

A table column can be extracted with:

Sales[Amount]

A list item is accessed by a zero-based index:

Values{0}

A table row can also be accessed by index, returning a record:

Sales{0}[Amount]

An invalid field name or index produces an error. The ? operator can sometimes be used for optional access, but it should not be treated as a substitute for understanding the data structure.

5. Lists

A list is an ordered sequence enclosed in braces:

{10, 20, 30, 40}

Lists may contain different types, although homogeneous lists are easier to process reliably:

{"North", "South", "East", "West"}

Important list functions include:

List.Count({10, 20, 30})
List.Sum({10, 20, 30})
List.Max({10, 20, 30})
List.First({10, 20, 30})
List.Last({10, 20, 30})
List.Distinct({"A", "B", "A"})

List.Transform applies a function to every item:

List.Transform({1, 2, 3}, each _ * 10)

The underscore _ represents the current item in an each expression. The result is {10, 20, 30}.

List.Select filters a list:

List.Select({1, 2, 3, 4, 5}, each _ > 3)

This returns {4, 5}.

Lists are useful for parameter values, lookup collections, dynamic column names and intermediate calculations. However, converting a very large table to a list can consume substantial memory and may reduce query performance.

6. Records

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

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

Fields are accessed by name:

Order[Quantity]

Records can be created or modified using functions such as Record.AddField and Record.Field:

Record.AddField([Name = "Amina"], "Department", "Finance")

The each keyword is especially important when working with table rows. In the following expression, each row is treated as a record:

Table.AddColumn(Source, "GrossAmount", each [Quantity] * [UnitPrice], type number)

The expression [Quantity] * [UnitPrice] reads fields from the current row record.

Useful record functions include:

Record.FieldNames(MyRecord)
Record.Field(MyRecord, "Region")
Record.HasFields(MyRecord, "Region")
Record.ToTable(MyRecord)

7. Tables

A table is a structured value made up of rows and columns. Each row can be understood as a record, while a column can be understood as a list. This relationship explains why M provides both Table. and List. functions.

A small table can be created with #table:

#table(
    {"Product", "Quantity", "Price"},
    {{"A", 2, 10}, {"B", 3, 15}}
)

Common table operations include:

Table.SelectRows(Source, each [Quantity] > 0)
Table.SelectColumns(Source, {"Product", "Quantity"})
Table.RemoveColumns(Source, {"UnwantedColumn"})
Table.RenameColumns(Source, {{"Qty", "Quantity"}})
Table.Sort(Source, {{"Quantity", Order.Descending}})

Adding a calculated column:

Table.AddColumn(
    Source,
    "LineValue",
    each [Quantity] * [Price],
    type number
)

Grouping data:

Table.Group(
    Source,
    {"Region"},
    {{"TotalSales", each List.Sum([Sales]), type number}}
)

The expression [Sales] inside the aggregation refers to the grouped table's Sales column. The result is a table with one row per region.

Joining tables is commonly performed with Table.NestedJoin:

Table.NestedJoin(
    Sales, {"ProductID"},
    Products, {"ProductID"},
    "ProductDetails", JoinKind.LeftOuter
)

The join creates a nested table column. Table.ExpandTableColumn can then expose selected fields from that nested table.

8. Functions and the each Shorthand

A function accepts zero or more parameters and returns a value. A function literal is written with an arrow:

(x as number) as number => x * 2

It can be assigned to a name:

let
    DoubleNumber = (x as number) as number => x * 2,
    Result = DoubleNumber(7)
in
    Result

The result is 14.

The each keyword is shorthand for an anonymous function. These expressions are equivalent in common contexts:

each [Amount] * 1.2

(_) => _[Amount] * 1.2

An explicit parameter can improve clarity when nested functions are used:

(row as record) as number => row[Quantity] * row[UnitPrice]

Parameters may have type declarations and optional values:

(amount as number, optional rate as number) as number =>
    if rate = null then amount else amount * rate

Functions are first-class values. They can be stored in variables, passed to other functions and returned from functions. This supports reusable transformation logic.

9. Scope, Names and Evaluation

Scope identifies where a name is available. Names defined in a let expression are visible to later expressions within that let expression. Function parameters are visible within the function body. A name defined inside a function does not automatically exist outside it.

Example:

let
    TaxRate = 0.2,
    AddTax = (amount as number) as number => amount * (1 + TaxRate),
    Result = AddTax(100)
in
    Result

TaxRate is available to the function because the function is defined within the surrounding scope. This is an example of a closure. Although useful, hidden dependencies can make code harder to test. Passing important assumptions as parameters may be preferable in reusable business logic.

M permits names that contain spaces when quoted, but stable names without spaces are often easier to reuse in custom functions. Automatically generated step names such as #"Changed Type" are valid; developers may rename them when doing so improves maintainability.

10. Conditional Logic and Error Handling

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

if [Sales] >= 10000 then "High" else "Standard"

Conditions must evaluate to a logical value. Nested conditions are possible, but a lookup table or a custom function may be clearer when many categories exist.

Errors are different from nulls. An error indicates that evaluation failed; null indicates absence of a value. M provides try for controlled error handling:

try Number.From([Amount])

A try expression returns a record containing fields such as HasError, Value and Error. A practical pattern is:

Table.AddColumn(
    Source,
    "SafeAmount",
    each try Number.From([Amount])[Value],
    type number
)

A more explicit approach can replace invalid values with null:

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

Replacing every error with null can conceal data-quality problems. A professional solution should quantify errors, investigate their cause and document the treatment.

11. Standard Library Functions

M includes namespaces containing standard functions. Some frequently used groups are shown below.

Examples of text standard-library functions:

Text.Trim("  North  ")
Text.Upper("uk")
Text.Replace("AB-123", "-", "")
Text.Contains("Power BI", "BI")

Culture can affect conversion and formatting. For example, decimal separators and date conventions vary by region. When importing files or converting text, explicitly select an appropriate locale where the interface or function supports it. Otherwise, a value such as 01/02/2025 may be interpreted differently in different environments.

12. Editing the Advanced Editor

The Advanced Editor displays the complete M expression for a query. It is opened from the Power Query Editor through the relevant query-management command. The exact menu location can vary by Power BI version, but the purpose is consistent: view and edit the query as code.

A disciplined editing workflow is:

1. Duplicate the query or save a version before major changes.
2. Read the existing source and identify the final output step.
3. Make one logical change at a time.
4. Use clear step names and consistent indentation.
5. Check that commas separate steps and that the in expression returns the intended step.
6. Validate the query before moving to another transformation.
7. Test with representative data, including nulls, errors, duplicates and empty inputs.

A typical manually formatted query is:

let
    Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content],
    CleanNames = Table.TransformColumnNames(Source, Text.Trim),
    Typed = Table.TransformColumnTypes(
        CleanNames,
        {
            {"OrderDate", type date},
            {"Quantity", Int64.Type},
            {"UnitPrice", type number}
        }
    ),
    AddedValue = Table.AddColumn(
        Typed,
        "LineValue",
        each [Quantity] * [UnitPrice],
        type number
    ),
    Filtered = Table.SelectRows(AddedValue, each [LineValue] > 0)
in
    Filtered

The graphical editor may generate step names with spaces and escaped identifiers. This is valid M. Manual editing should preserve the exact names or update every reference consistently.

13. Troubleshooting M Code

Syntax errors

Typical causes include:

• Missing commas between let steps.
• Missing closing parentheses, brackets or braces.
• Incorrect quotation marks.
• A missing in expression.
• Incorrect capitalization of a function name.

For example, this is invalid because the comma after A = 10 is missing:

let
    A = 10
    B = 20
in
    A + B

Expression errors

An expression error occurs when the syntax is valid but the requested operation is invalid. Examples include dividing by zero, referencing a missing field or applying a text function to an incompatible value.

Type errors

A column containing mixed values may prevent numerical aggregation. Apply an explicit type and inspect conversion errors rather than assuming that all source values are valid.

Missing-field errors

A query may work until a source system renames or removes a column. Robust solutions can validate required columns using Table.HasColumns before performing transformations:

if Table.HasColumns(Source, {"CustomerID", "Amount"})
then Source
else error "Required columns are missing"

Privacy and source-combination issues

Power Query privacy levels help prevent unintended data disclosure when combining sources. A query can fail or behave differently if privacy settings restrict source combination. Privacy levels should be configured according to organizational policy, not simply disabled to make an error disappear.

Performance problems

Common causes include loading unnecessary columns, filtering after expensive operations, repeated source access, row-by-row operations over very large data and premature buffering. Where query folding is supported, filters and projections should generally be pushed toward the source. Table.Buffer should be used cautiously because it can increase memory use and prevent beneficial source folding.

14. Applied Case: Regional Sales Consolidation

The following is an illustrative case.

Background

A distributor receives monthly sales files from three regional offices. The files have inconsistent column names, occasional blank quantities and product codes containing extra spaces. Management requires a single Power BI table with reliable dates, quantities, revenue and region labels.

Problem

The source files have these differences:

• One file uses Qty, another uses Quantity.
• Product codes may contain leading or trailing spaces.
• Some amounts are stored as text.
• Invalid amounts should not stop the entire refresh.
• The region is known from the file source but is not included in every file.

Approach

A custom function can standardize one file, while a separate query invokes that function for every file in a folder. A simplified transformation function is:

(fileTable as table, regionName as text) as table =>
let
    StandardNames = Table.RenameColumns(
        fileTable,
        {{"Qty", "Quantity"}},
        MissingField.Ignore
    ),
    CleanProduct = Table.TransformColumns(
        StandardNames,
        {{"ProductCode", each Text.Trim(Text.From(_)), type text}}
    ),
    SafeAmount = Table.AddColumn(
        CleanProduct,
        "AmountNumber",
        each try Number.From([Amount]) otherwise null,
        type number
    ),
    AddRegion = Table.AddColumn(
        SafeAmount,
        "Region",
        each regionName,
        type text
    ),
    AddRevenue = Table.AddColumn(
        AddRegion,
        "Revenue",
        each [Quantity] * [AmountNumber],
        type number
    )
in
    AddRevenue

The function establishes a repeatable contract: it accepts a table and a region name, standardizes the quantity field when necessary, trims product codes, safely converts amounts, adds the region and calculates revenue.

Analysis and outcome

The consolidated query can combine the transformed results using Table.Combine. Invalid amounts become null rather than terminating refresh, but a separate quality-report query should count those null conversions. The resulting model contains consistent fields and can support regional revenue analysis.

Learning points

1. Functions reduce duplication across similar files.
2. MissingField.Ignore is useful when controlled source variation is expected, but it should not hide serious schema failures.
3. try ... otherwise null protects refreshes but must be accompanied by monitoring.
4. Applying types explicitly improves downstream modelling.
5. Source-specific metadata, such as region, can be passed as a function parameter.

15. Governance, Ethics and Professional Practice

M code participates in an organization’s data pipeline and therefore has governance implications. Developers should:

• Avoid embedding passwords, access tokens or confidential values in query code.
• Use approved connectors and credential management mechanisms.
• Document transformations that alter financial, customer or regulatory data.
• Preserve auditability by naming steps clearly and retaining source definitions.
• Apply least-privilege access to data sources.
• Validate privacy settings when combining organizational and external data.
• Avoid silently deleting invalid rows; report the number and nature of rejected records.
• Consider whether transformations could create unfair or misleading analytical results.

AI-assisted code generation can help produce M expressions, but generated code must be reviewed. The developer remains responsible for syntax, privacy, correctness, source permissions, performance and business interpretation. Sensitive source data should not be pasted into unapproved AI tools.

16. Best Practices Checklist

A reliable M query generally follows these practices:

• Give every important step a meaningful name.
• Keep source access, cleansing, transformation and output stages conceptually separate.
• Select only required columns as early as practical.
• Filter early when query folding permits it.
• Apply explicit data types.
• Use functions for repeated logic.
• Handle nulls and conversion errors deliberately.
• Avoid unnecessary buffering.
• Test with empty tables, duplicate keys, missing fields and invalid values.
• Preserve a clear final in expression.
• Use comments to document assumptions and exceptions.
• Monitor refresh duration and data-quality counts.

Chapter Summary

Power Query M is a case-sensitive, expression-oriented language in which queries return values. The central let ... in structure assigns names to intermediate expressions and returns a final result. M supports primitive values such as numbers, text, logical values, dates and null, as well as structured values including lists, records, tables and functions.

Operators perform arithmetic, comparison, logical evaluation, concatenation and structured-data access. Lists are ordered collections, records contain named fields and tables combine rows and columns. The each shorthand expresses anonymous functions, while custom functions promote reuse and consistency. Scope controls where names and parameters are available.

The standard library supplies functions for text, numbers, dates, lists, records and tables. Conditional expressions and try support controlled handling of business rules and data-quality problems. The Advanced Editor provides direct access to the complete query and is essential for precise editing and troubleshooting.

Professional M development requires more than code that runs. It requires explicit types, readable structure, awareness of query folding and performance, careful privacy management, documented assumptions and monitoring of rejected or invalid data.

Key Terms

M language: The functional data-transformation language used by Power Query.

Expression: Code that evaluates to a value.

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

Value: The result produced by evaluating an expression.

Type: A classification describing the permitted form and behavior of a value.

Null: A value representing the absence of a value.

List: An ordered collection of values.

Record: A collection of named fields.

Table: A structured value containing rows and columns.

Function: A reusable expression that accepts parameters and returns a value.

Scope: The region in which a name or parameter is available.

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

Quoted identifier: An escaped name written in the form #"...".

each: Shorthand for an anonymous function, commonly used for row transformations.

Query folding: The process of delegating compatible transformations to the data source.

Type conversion: The process of changing a value from one data type to another.

try: An expression used to capture or replace evaluation errors.

Advanced Editor: The interface for viewing and editing a query’s complete M expression.

Privacy level: A setting governing how data sources may be combined.

Review Questions

Short Answer Questions

1. What is the purpose of the in clause in a let ... in expression?
2. Distinguish a null value from an empty text string.
3. What does the each keyword represent?
4. Why are explicit column types important in Power Query?
5. State two common causes of syntax errors in M.

Long Answer Questions

1. Explain how lists, records and tables differ, and describe one practical use for each.
2. Explain M scope using a let ... in expression and a custom function as examples.
3. Discuss the role of standard-library functions in text, date and table transformation.
4. Explain how try ... otherwise can improve refresh reliability and why it can also create governance risks.
5. Describe a systematic workflow for editing and troubleshooting code in the Advanced Editor.

Analytical / Application Questions

1. A source table contains an Amount column with valid numbers, blank cells and values such as "unknown". Design an M strategy that preserves refresh reliability while allowing the organization to monitor data-quality failures.
2. Three monthly files use different names for the quantity column and one file lacks a region field. Propose a function-based transformation design and explain how it should handle expected variation versus serious schema failure.
3. A query becomes slow after a developer adds several row-level transformations and Table.Buffer. Analyze possible causes and recommend an investigation and optimization plan.

Multiple Choice Questions with Answers

Question 1

What value is returned by the following M expression? let A = 10, B = 5, C = A - B in C

A. 5

B. 10

C. 15

D. 50

Correct Answer: A. 5

Question 2

Which notation represents a list in M?

A. [A = 1, B = 2]

B. {1, 2, 3}

C. (1, 2, 3)

D. <1, 2, 3>

Correct Answer: B. {1, 2, 3}

Question 3

What does the in clause specify in a let ... in expression?

A. The data source credential

B. The final value returned by the expression

C. The type of every step

D. The privacy level of the query

Correct Answer: B. The final value returned by the expression

Question 4

Which expression accesses the first item in a list named Values?

A. Values[0]

B. Values{0}

C. Values(0)

D. Values.0

Correct Answer: B. Values{0}

Question 5

What is the main purpose of Table.AddColumn?

A. To delete a table

B. To add a calculated or supplied column to a table

C. To convert a table into a record automatically

D. To change a source credential

Correct Answer: B. To add a calculated or supplied column to a table

Question 6

Which expression safely converts an invalid value to null?

A. Number.From([Amount]) otherwise null

B. try Number.From([Amount]) otherwise null

C. null Number.From([Amount])

D. Number.Try([Amount]) = null

Correct Answer: B. try Number.From([Amount]) otherwise null

Question 7

In the expression each [Quantity] * [UnitPrice], what does each provide?

A. A database connection

B. An anonymous function applied to the current row

C. A new table schema

D. A text conversion rule

Correct Answer: B. An anonymous function applied to the current row

Question 8

Which operator is normally used to concatenate text in M?

A. +

B. &

C. ||

D. .

Correct Answer: B. &

Question 9

Which M value represents the absence of a value?

A. false

B. 0

C. null

D. ""

Correct Answer: C. null

Question 10

Why should Table.Buffer be used cautiously?

A. It always deletes duplicate rows

B. It can increase memory use and interfere with query folding

C. It changes every column to text

D. It disables all M functions

Correct Answer: B. It can increase memory use and interfere with query folding