Understanding the M Formula Language: Syntax, Data Types, and Expression Basics

Introduction

You've been clicking through Power Query's graphical interface — renaming columns, filtering rows, changing data types — and everything looks fine. But then you open the Advanced Editor and see something like this:

let
    Source = Csv.Document(File.Contents("C:\sales_data.csv"), [Delimiter=",", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"OrderDate", type date}, {"Revenue", type number}})
in
    #"Changed Type"

If that looks like a foreign language, you're in exactly the right place. This is M — the formula language that powers every single transformation you make in Power Query. Every click you've ever made in the UI has been silently writing M code behind the scenes. Once you understand how M works, you stop being a passenger in Power Query and start being the driver.

By the end of this lesson, you'll be able to read M code without panic, write simple M expressions from scratch, understand why M handles data differently than Excel or SQL, and use the Advanced Editor confidently for basic tasks.

What you'll learn:

• How M's let...in expression structure works and why it exists
• The core data types in M and how to work with them correctly
• How to write and chain M expressions step by step
• The difference between case-sensitive M syntax and what you're used to in Excel
• How to use the Advanced Editor to inspect and modify M code directly

---

Prerequisites

You should have Power Query available — either through Excel (Data tab → Get Data) or Power BI Desktop (Home → Transform Data). You don't need prior programming experience. Basic familiarity with what Power Query does — loading data, renaming columns, filtering — is helpful but not required.

---

What Is M, Exactly?

M is a functional formula language designed specifically for data transformation. It was built by Microsoft to describe how to get and shape data, not how to store it or calculate statistics. Think of it less like a traditional programming language and more like an extremely powerful recipe: you write a series of instructions that Power Query follows to produce a clean, transformed dataset.

The formal name is "Power Query Formula Language," but everyone calls it M. It lives inside Power Query's engine and runs every time your query refreshes.

Here's what makes M different from tools you might already know:

• Unlike Excel formulas, M operates on entire tables, lists, and records — not individual cells.
• Unlike SQL, M doesn't query a database with declarative statements. M describes a transformation pipeline.
• Unlike Python or JavaScript, M is lazy — it only evaluates what it needs to, when it needs to. This makes it efficient but also occasionally surprising.

The single most important thing to understand before writing a single line of M: M is case-sensitive. Text.Upper works. text.upper throws an error. TABLE.TRANSFORMCOLUMNTYPES does nothing useful. This trips up nearly every beginner, so keep it in the back of your mind at all times.

---

The `let...in` Expression: The Skeleton of Every M Query

Open Power Query, load any dataset, then go to Home → Advanced Editor. What you'll see is almost always structured like this:

let
    Step1 = some expression,
    Step2 = some transformation of Step1,
    Step3 = some transformation of Step2
in
    Step3

This is the let...in expression, and it is the backbone of M. Let's understand each part.

The let block is where you define named steps. Each step has a name (called a variable or binding), followed by an equals sign, followed by an expression that produces a value. Steps are separated by commas. The in block contains a single expression — usually the name of the last step — which is what the query returns as its final result.

Here's a concrete, minimal example:

let
    RawRevenue = 1250.75,
    TaxRate = 0.08,
    TaxAmount = RawRevenue * TaxRate,
    TotalRevenue = RawRevenue + TaxAmount
in
    TotalRevenue

You can type this directly into the Advanced Editor (after creating a blank query via Home → New Source → Other Sources → Blank Query). The query returns 1350.81. Each step builds on the previous one. If you change RawRevenue to 2000, every downstream step recalculates automatically — because each step is just an expression that references earlier names.

This is what "functional" means in practice: each step is an independent expression, not a command that modifies some shared memory. Step3 doesn't change Step2 — it creates a new value based on Step2. This immutability makes M queries predictable and easy to debug.

Tip: The names of your steps in the let block correspond directly to the "Applied Steps" panel you see in the Power Query editor. When Power Query generates a step called #"Changed Type", it's just writing a named binding in the let block.

---

Step Names with Special Characters

You've probably noticed that auto-generated step names often look like #"Promoted Headers" instead of just PromotedHeaders. The #"..." syntax is M's way of allowing step names (and column names) that contain spaces, special characters, or reserved keywords.

let
    #"Raw Sales Data" = Csv.Document(...),
    #"Removed Blank Rows" = Table.SelectRows(#"Raw Sales Data", each [Revenue] <> null)
in
    #"Removed Blank Rows"

The #"..." wrapper just tells M: "treat everything inside these quotes as a single identifier name." You can name a step #"My Step With Spaces" if you want. For steps you write yourself, it's cleaner to use simple camelCase or PascalCase names without spaces — CleanedSales instead of #"Cleaned Sales". But you need to read the quoted form fluently because Power Query generates it constantly.

---

M's Core Data Types

Every value in M has a type. Getting types wrong is the number one source of errors in Power Query, so let's walk through each major type clearly.

Primitive Types

These are single values:

Number — Any numeric value, integer or decimal.

let
    UnitPrice = 49.99,
    Quantity = 12,
    LineTotal = UnitPrice * Quantity   // returns 599.88
in
    LineTotal

Text — A string of characters, always wrapped in double quotes.

let
    FirstName = "Sarah",
    LastName = "Chen",
    FullName = FirstName & " " & LastName   // returns "Sarah Chen"
in
    FullName

The & operator concatenates text in M, just like in Excel.

Logical — true or false, always lowercase.

let
    Revenue = 15000,
    IsHighValue = Revenue > 10000   // returns true
in
    IsHighValue

Date, Time, DateTime, DateTimeZone, Duration — M has rich built-in support for time-based values. A date literal looks like #date(2024, 3, 15). A time literal looks like #time(14, 30, 0). A datetime combines them: #datetime(2024, 3, 15, 14, 30, 0).

let
    OrderDate = #date(2024, 1, 10),
    ShipDate = #date(2024, 1, 14),
    DaysToShip = Duration.Days(ShipDate - OrderDate)   // returns 4
in
    DaysToShip

Null — The absence of a value, written as null (lowercase). Null is not zero, not an empty string, not false. It's nothing. Many functions behave differently when they encounter null, and you'll spend real time in Power Query handling null values.

Structured Types

These are where M becomes powerful for data work.

List — An ordered sequence of values, wrapped in {}.

let
    SalesRegions = {"Northeast", "Southeast", "Midwest", "West"},
    RegionCount = List.Count(SalesRegions)   // returns 4
in
    RegionCount

A list can contain any type of value — numbers, text, dates, even other lists. You access individual items by position (zero-indexed): SalesRegions{0} returns "Northeast".

Record — A set of named fields, wrapped in []. Think of a record as a single row of data with labeled columns.

let
    TopCustomer = [
        Name = "Meridian Industries",
        AnnualRevenue = 4200000,
        Tier = "Enterprise",
        IsActive = true
    ],
    CustomerName = TopCustomer[Name]   // returns "Meridian Industries"
in
    CustomerName

You access record fields using [FieldName] notation. Records are central to M — when you use each [ColumnName] in a transformation, you're accessing a field from a record that represents one row of your table.

Table — A structured collection of rows and columns, the type you'll work with most in Power Query. Tables are typically produced by source connections (Csv.Document, Excel.Workbook, etc.) and transformed by Table.* functions.

Warning: Unlike Excel, M does not implicitly convert between types. If you try to add a text value to a number — even if that text looks like a number — you'll get an error. You must explicitly convert: Number.FromText("42") + 8 returns 50, but "42" + 8 throws a type mismatch error.

---

Writing Expressions: The Building Blocks

An expression in M is anything that evaluates to a value. 1 + 1 is an expression. "Hello" is an expression. Table.RowCount(SomeTable) is an expression. Everything in M is an expression.

Arithmetic and Comparison

let
    GrossMargin = 0.42,
    TargetMargin = 0.35,
    AboveTarget = GrossMargin > TargetMargin,   // true
    MarginGap = GrossMargin - TargetMargin       // 0.07
in
    MarginGap

Standard operators: +, -, *, / for math. = for equality (not assignment — in M, = in an expression means "is equal to"). <> for not equal. <, >, <=, >= for comparisons.

Conditional Expressions with `if`

M uses if...then...else as a full expression, not a statement. It always requires both a then and an else branch.

let
    MonthlyRevenue = 87500,
    PerformanceLabel = if MonthlyRevenue >= 100000 then "On Target"
                       else if MonthlyRevenue >= 75000 then "Near Target"
                       else "Below Target"
in
    PerformanceLabel   // returns "Near Target"

Because if...then...else is an expression, it can be nested inline like this. Don't look for a standalone if without an else — it doesn't exist in M.

Functions: The Core of M

Almost everything interesting in M happens through functions. A function call looks like:

FunctionName(argument1, argument2, ...)

Functions in M are organized into namespaces by the type they operate on. Table.* functions work on tables. Text.* functions work on text. List.* functions work on lists. Date.* functions work on dates. This naming convention makes M surprisingly readable once you know the pattern.

let
    ProductName = "  Wireless Keyboard Pro  ",
    Cleaned = Text.Trim(ProductName),           // "Wireless Keyboard Pro"
    Uppercased = Text.Upper(Cleaned),           // "WIRELESS KEYBOARD PRO"
    WordCount = List.Count(Text.Split(Cleaned, " "))  // 3
in
    WordCount

Notice how we're chaining operations by using the result of one step as the input to the next. This is the M way.

Tip: The Power Query function reference is built into the tool. In the formula bar of any blank query step, type = #shared and press Enter. You'll get a table of every available M function with descriptions. It's the fastest way to explore what's available.

---

The `each` Shorthand

You'll see each constantly in M, and it confuses beginners. Here's the plain-English explanation: each is shorthand for writing a small, anonymous function that applies to each row (or each item in a list).

When you filter rows in Power Query's UI, it might generate:

Table.SelectRows(SalesData, each [Region] = "Northeast")

The each [Region] = "Northeast" part is a shorthand function. It means: "for each row in this table, check whether the Region field equals Northeast." The underscore _ represents the current row (you'll occasionally see each _[Region] when the context is explicit), but in most table operations, [ColumnName] directly accesses the field of the current row.

Without the each shorthand, this would be written:

Table.SelectRows(SalesData, (row) => row[Region] = "Northeast")

Both forms are valid M. The each shorthand is just more concise for the common case of working with individual rows or items.

---

Using the Advanced Editor

Now let's put this into practice. Open Power Query in Excel or Power BI and follow these steps:

1. In Excel: Go to the Data tab → Get Data → Launch Power Query Editor
2. In the Power Query Editor: Home tab → New Source → Other Sources → Blank Query
3. You'll see a new query with a formula bar. Click on the formula bar area.
4. Now go to Home → Advanced Editor

You should see a window with:

let
    Source = ""
in
    Source

Replace everything with this:

let
    Transactions = #table(
        {"OrderID", "Product", "Quantity", "UnitPrice"},
        {
            {1001, "Laptop Stand", 3, 29.99},
            {1002, "USB Hub", 7, 19.99},
            {1003, "Monitor Cable", 2, 12.50},
            {1004, "Webcam", 1, 89.99}
        }
    ),
    WithRevenue = Table.AddColumn(Transactions, "Revenue", each [Quantity] * [UnitPrice], type number),
    HighValueOrders = Table.SelectRows(WithRevenue, each [Revenue] > 50)
in
    HighValueOrders

Click Done. You should see a table with two rows: the USB Hub order ($139.93) and the Webcam order ($89.99).

Walk through what just happened:

• #table(...) is M's way of creating a table literal directly in code, specifying column names and then rows of data
• Table.AddColumn adds a new column called "Revenue" where each row's value is Quantity × UnitPrice
• Table.SelectRows filters to only rows where Revenue exceeds 50
• Each step builds on the previous one by name

---

Hands-On Exercise

Create a new blank query in Power Query and build the following M query from scratch in the Advanced Editor. Read through it carefully before typing — make sure you understand what each step does.

Scenario: You have a list of employee names with inconsistent formatting from a data export. Your job is to clean them up and flag anyone from the Sales department.

let
    RawEmployees = #table(
        {"EmployeeID", "FullName", "Department", "AnnualSalary"},
        {
            {101, "  james PARKER  ", "Sales", 72000},
            {102, "LINDA morrison", "Engineering", 95000},
            {103, "  Tom Nguyen", "Sales", 68000},
            {104, "RACHEL kim  ", "Marketing", 81000},
            {105, "derek WALSH  ", "Engineering", 103000}
        }
    ),
    CleanedNames = Table.TransformColumns(
        RawEmployees,
        {"FullName", each Text.Proper(Text.Trim(_)), type text}
    ),
    WithSalesFlag = Table.AddColumn(
        CleanedNames,
        "IsSales",
        each [Department] = "Sales",
        type logical
    ),
    SortedBySalary = Table.Sort(WithSalesFlag, {{"AnnualSalary", Order.Descending}})
in
    SortedBySalary

Your tasks:

1. Type this in and verify it produces a clean, sorted table
2. Modify the filter to flag "Engineering" employees instead of "Sales"
3. Add a new step called HighEarners after SortedBySalary that uses Table.SelectRows to keep only employees earning more than $80,000. Update the in clause to return HighEarners.
4. Try changing Text.Proper to Text.Upper in the CleanedNames step and observe the difference

---

Common Mistakes & Troubleshooting

"Expression.Error: The name 'X' wasn't found." You referenced a step name that doesn't exist, or you misspelled it. Check capitalization. If you deleted a step in the Applied Steps panel, any step that references it will break with this error.

"Expression.Error: We cannot convert the value 'X' to type Number." You're performing a numeric operation on text, or a date operation on something that isn't a date. Use Number.FromText(), Date.From(), or DateTime.From() to explicitly convert.

"Token Eof expected." M couldn't finish parsing your expression. Usually this means a missing comma between steps in the let block, or a missing in clause. Also triggered by mismatched parentheses or brackets.

Forgetting the comma between let steps. Every step in a let block except the last one needs a comma after it. The last step before in does not get a comma. This is one of the most common mechanical errors.

Case sensitivity surprises. table.addcolumn is not the same as Table.AddColumn. The first will fail silently or error. Always capitalize function names exactly as documented.

Confusing = in assignment vs. comparison. In a let step, Revenue = 1000 means "name this value Revenue." In an if expression or Table.SelectRows, [Status] = "Active" means "check if Status equals Active." M uses = for both — context determines meaning. This is different from most programming languages where == is used for comparison.

---

Summary & Next Steps

You've covered a lot of ground. Let's recap the essentials:

• Every Power Query query is a let...in expression where named steps build on each other
• M is case-sensitive, functional, and expression-based — every piece of code evaluates to a value
• The core primitive types are Number, Text, Logical, Date/Time, and Null; the core structured types are List, Record, and Table
• Functions are organized by namespace (Table.*, Text.*, List.*, etc.) and do the heavy lifting
• each is shorthand for a row-level function applied across a table or list
• The Advanced Editor gives you direct access to M code, and you should use it

This foundation will make every Power Query task you do going forward more intentional. Instead of clicking blindly through the UI and hoping the right code gets generated, you'll understand what the generated code means — and you'll be able to modify it when the UI can't do exactly what you need.

Recommended next steps:

1. Custom Columns with M Expressions — Learn to write conditional logic and multi-step calculations in the Add Column → Custom Column dialog
2. Working with M's Table Functions — Go deep on Table.SelectRows, Table.AddColumn, Table.TransformColumns, and Table.Group
3. Error Handling in M — Learn try...otherwise to make your queries resilient to bad data
4. Parameters and Dynamic Queries — Use M to build queries that adapt to user inputs or changing file paths