Power BI Advanced Series · Power Query & M Language · by Raushan Ranjan, MCT

Power Query is an incredibly powerful tool for data transformation and preparation within Microsoft Excel and Power BI. While its user-friendly interface allows for much of this magic to happen with clicks, the true power and flexibility lie beneath the surface, in a language called M.

Understanding the M language is like gaining a superpower for your data workflows. It allows you to go beyond the clicks, troubleshoot complex queries, and craft highly customized transformations. This blog post will break down the foundational concepts of the M language, making it accessible for anyone looking to unlock Power Query's full potential.

We'll explore:

  • The Syntactical Requirements of M Code: The fundamental rules for writing M.
  • Crafting Simple Queries in the Advanced Editor: Getting hands-on with M.
  • Key Object Structures in M: Understanding the different data types and how to work with them.

Let's dive in!

Part 1: The Essential Syntax of M Code

Think of M code as a series of steps, each building upon the last to transform your data. Here's how it's structured:

1. Steps as Variables

Every single action you perform in Power Query, whether it's filtering rows, merging columns, or adding a new one, is represented as a step in the M language. Each step acts as a variable, capable of holding any type of value – be it a number, a table, or even a function itself.

Crucially, these steps can then be used as inputs for subsequent steps. This chaining of operations is what makes M so powerful for building complex data transformations.

2. Naming Conventions for Steps

When you create a step, Power Query automatically assigns it a name. Here are some key naming rules:

  • No Spaces (Ideally): For simplicity and readability, it's best to avoid spaces in your step names.
  • Handling Spaces with Care: If a step name must contain spaces (perhaps due to an auto-generated name or a specific need), you need to enclose the name in quotation marks and prefix it with a hash symbol (e.g., #"Filtered Rows"). This tells M that the entire string is the step name.

3. Commas at the End of Steps

This is a simple yet vital rule: every step, except for the very last one, must end with a comma (,). This comma acts as a separator, indicating to M that another step follows. Forgetting a comma will result in a syntax error.

4. The let and in Keywords: Defining Your Query

Every M query begins with the let keyword and ends with the in keyword. These act as bookends for your entire transformation logic:

  • let: This keyword signals the start of your query, where you will declare all your variables (steps).
  • in: This keyword concludes the declaration section and, most importantly, specifies which step's output should be returned as the final result of your query. Whatever step you place after in is what Power Query will display as the final transformed data.

5. Order of Steps: Flexibility in Code, Clarity in UI

While the Power Query user interface neatly arranges your steps in a logical, coherent order, the underlying M code offers more flexibility. Interestingly, the M code does not strictly require the steps to be in the same sequential order as they appear in the UI. You can jumble the steps in the Advanced Editor, and as long as your syntax is correct and dependencies are met, the query will still function correctly.

However, for readability and maintainability, it's generally good practice to keep your steps in a logical flow within the Advanced Editor as well.

6. Bottom-Up Evaluation: How M Processes Your Query

Contrary to a top-down execution, Power Query evaluates M queries from bottom up. This means it starts with the output specified after the in keyword. From there, it traces back and finds all the necessary preceding steps that contribute to that final output, executing them as needed. This efficient evaluation mechanism ensures that only the relevant parts of your query are processed.

Part 2: Writing a Simple Query in the Advanced Editor

Let's put these syntactical rules into practice by crafting a basic M query from scratch in Power Query's Advanced Editor.

Imagine we want to create a list, count its items, and then perform a simple multiplication.

Here's how you might write it: 

let
    MyList = {"Learning with Raushan", "Power BI Developer", 1107},
    ListCount = List.Count(MyList),
    Multiply = ListCount * 2
in
    Multiply

Let's break down this example:

  • MyList = {"Learning with Raushan", "Power BI Developer", 1107},: This step defines a list named MyList containing a mix of text and numbers. Notice the comma at the end, as it's not the final step.
  • ListCount = List.Count(MyList),: This step uses the built-in List.Count function to count the number of items in MyList. The result (which would be 3) is stored in the ListCount variable. Again, a comma follows.
  • Multiply = ListCount * 2: This step takes the ListCount (which is 3) and multiplies it by 2. The result (6) is stored in the Multiply variable. There's no comma here because it's the last step declared before in.
  • in Multiply: This tells Power Query that the final output of this entire query should be the value stored in the Multiply step, which is 6.

This simple example demonstrates how variables (steps) can be created and then referenced in subsequent steps to build a logical flow.

Part 3: Understanding Different Object Structures in M Language

The M language works with various types of data structures, each with its own characteristics and uses. Understanding these "objects" is crucial for effective M coding.

1. Lists: The Single-Column Collection

  • Definition: A list in M is a simple, one-columnar data structure. Think of it as an ordered collection of items.
  • Key Distinction: Unlike a single-column table, a list cannot have more columns once defined. It's strictly a single column of data.
  • Defining a List Manually: You define a list by enclosing its values within curly brackets {}.
  • Example: {1, 2, 3} or {"Apple", "Banana", "Cherry"}.
  • Extracting a List:
    • From a Table: You can extract a specific column from an existing table as a list. Use the table name followed by the column name in square brackets.
    • Example: If you have a table named Source with a column named Name, you can get a list of names using Source[Name].
    • Using a Function: Many built-in M functions return lists. For example, Table.ColumnNames can extract all column names from a table as a list.
    • Example: Table.ColumnNames(Source) would return a list of all column headers in the Source table.

2. Records: The Single-Row Structure

  • Definition: A record represents a single row of data. It's a collection of key-value pairs, where each "key" is like a column header, and the "value" is the data in that column for that specific row.
  • Extracting a Record: You can extract a specific row from a table as a record. Power Query uses a 0-based index for rows, meaning the first row is index 0, the second is index 1, and so on. You use curly brackets {} to specify the row number.
  • Example: If Source is your table, Source{2} would extract the third row (because it's 0-indexed) as a record.
  • Defining a Record Manually: You define a record by listing key-value pairs within square brackets [].
  • Example: [Name = "Raushan", City = "Noida", Age = 30]

3. Single or Scalar Values: The Atomic Unit

  • Definition: A single or scalar value is the most basic, individual unit of data. This could be a number, a piece of text, a date, a boolean (true/false), etc.
  • Extracting a Single Value:
    • From a List: You can extract a specific item from a list using its 0-based index within curly brackets.
    • Example: If MyList is {"Red", "Green", "Blue"}, then MyList{1} would extract "Green".
    • Combined Example (from a table): If Source is a table, Source[Age]{2} would first get the 'Age' column as a list, and then extract the item at index 2 from that list.
    • From a Record: You can extract a specific value from a record by referencing its key (column name) within square brackets.
    • Example: If MyRecord is [Name = "Raushan", City = "Noida"], then MyRecord[City] would extract "Noida".
    • Combined Example (from a table): Source{2}[Age] would first extract the third row as a record, and then get the value from the 'Age' field within that record.
  • Defining a Single Value Manually: You simply write the value directly.
    • Numbers: 1000, 3.14
    • Text: Enclosed in double quotes: "Apple", "Hello World"
    • Booleans: true, false

4. Functions: The Building Blocks of Transformation

  • Definition: Functions are pre-built capabilities or formulas within Power Query that allow you to perform specific operations and transform objects. M boasts over 800 functions, covering a vast range of data manipulation needs.
  • Viewing Functions: To get a glimpse of the sheer number of available functions, open the Advanced Editor in Power Query and create a new blank query. In the let section, simply type #shared and press "Done." This will return a record of all available functions and their types.
  • Learning Functions: Input and Output are Key: When working with functions, the most crucial aspects to understand are:
    • Input Requirements: What type of data does the function expect as input? Does it need a table, a list, a record, or a single value? Providing the wrong input type will result in an error.
    • Output Type: What kind of data does the function return? Does it give back a new table, a list, a number, or something else? Knowing the output helps you chain functions effectively.
  • Example: The function Table.RowCount(Source) requires a table as its input (here, the Source table) and returns a single number (the count of rows) as its output. If you try to pass a list or a record to Table.RowCount, it will throw an error because it expects a table.

Conclusion: Empowering Your Data Journey

By grasping these foundational concepts of the M language, you're not just performing steps in Power Query; you're understanding the very logic that drives your data transformations. This knowledge empowers you to:

  • Debug Queries More Effectively: Pinpoint exactly where an error might be occurring.
  • Customize Transformations: Write specific logic that the UI might not directly support.
  • Optimize Performance: Understand how M evaluates queries and potentially write more efficient code.
  • Learn New Functions Faster: With an understanding of input/output types, you can quickly grasp how new functions fit into your workflow.

So, next time you're in Power Query, take a peek at the Advanced Editor. The M language, once intimidating, will start to reveal its elegant and powerful structure, truly unlocking your data transformation capabilities. Happy querying!

Quick Knowledge Check

Q1. In an M let expression, Power Query evaluates the steps starting from the in keyword. What does this mean for a step that is defined but never referenced?

Show Answer

The unreferenced step is never evaluated — M is lazily evaluated. The engine traces backwards from the in output to find only the dependent steps. If a step is not referenced by the output (directly or transitively), it is skipped. This is why M can be efficient even in large, multi-step queries: only necessary work is performed.

Q2. A developer writes: each [Sales] > 1000 inside a Table.SelectRows call. What does the each keyword represent in M?

  • A) A loop that iterates over all columns in the table
  • B) A shorthand for a single-argument function (_) => _[Sales] > 1000, applied row by row
  • C) A DAX-style row context that evaluates a measure per row
  • D) A keyword that creates a new column called Sales
Show Answer

B. In M, each is syntactic sugar for a single-argument function where the argument is represented by _ (underscore). each [Sales] > 1000 is equivalent to (_) => _[Sales] > 1000. It is commonly used with Table.SelectRows, Table.AddColumn, and List.Select to write concise row-level expressions.

Q3. What is the difference between a list, a record, and a table in M?

Show Answer

A list is an ordered sequence: {1, 2, 3}. A record is a named key-value set: [Name="Alice", Age=30]. A table is a structured set of named, typed columns and rows — the most common output in Power Query. Functions prefixed List., Record., and Table. operate on their respective types. Mixing these types causes type mismatch errors that are easiest to debug in the Advanced Editor.

5 Things to Remember
  • M evaluates bottom-up (lazy) — starts from the in output and only computes referenced steps. Unreferenced steps are skipped.
  • each is shorthand for (_) => — the underscore is the current row/item. Use it with Table.SelectRows and Table.AddColumn.
  • Three core composite types: list {}, record [], table — confusing them creates type mismatch errors only visible in the Advanced Editor.
  • Comma on every let line except the last — the #1 M syntax error. The Advanced Editor colour-codes it; the formula bar does not.
  • Function queries make M reusable — wrap a let in (x as text) => and invoke via "Add Column → Invoke Custom Function."
📚 Resources