M Language Fundamentals: Syntax, Types, and Expressions

You've just inherited a dataset with messy sales data. Orders from different regions use different date formats, revenue figures include various currency symbols, and product categories are sometimes text, sometimes numbers. Your manager needs a clean, standardized report by tomorrow morning. You open Power Query, stare at the Advanced Editor, and realize you need to write custom M code to handle these transformations. But where do you start?

Learning M Language is like learning to speak directly to Power Query instead of relying on the point-and-click interface. While the GUI is powerful, M code gives you precise control over every transformation, allows you to create reusable functions, and enables complex logic that would be impossible through menus alone. Most importantly, understanding M helps you troubleshoot when automated transformations don't work as expected.

By the end of this lesson, you'll write M code with confidence, understanding exactly how your data transformations work under the hood. You'll be able to read and modify the code that Power Query generates, and create custom solutions for complex data challenges.

What you'll learn:

• How to read and write M Language syntax correctly
• The core data types available in M and when to use each one
• How to construct expressions that transform your data
• How to work with M's functional programming concepts
• How to debug common M Language errors

Prerequisites

This lesson assumes you have Power Query available (either through Excel, Power BI Desktop, or another Microsoft tool) and basic familiarity with the Power Query interface. You should know how to create a simple query and view the Advanced Editor. No programming experience is required—we'll build from the ground up.

Understanding M Language Structure

M Language is a functional programming language, which means it's built around functions that take inputs and return outputs without changing the original data. Think of it like a factory assembly line: each function is a station that receives materials, performs a specific operation, and passes the result to the next station.

Let's start by examining what happens when you perform a simple transformation. Create a new blank query in Power Query, then click Advanced Editor. You'll see this basic structure:

let
    Source = "Hello World"
in
    Source

Every M query follows this let...in pattern. The let section defines variables and their values, while the in section specifies what the query should return. This structure is like a recipe: you list your ingredients and preparation steps in the let section, then declare the final dish in the in section.

Let's break down the syntax rules:

Variable Assignment: Variables are assigned using the equals sign, and each assignment ends with a comma (except the last one):

let
    CustomerName = "Acme Corp",
    OrderDate = #date(2024, 3, 15),
    OrderTotal = 1250.00
in
    OrderTotal

Comments: Use // for single-line comments or /* */ for multi-line comments:

let
    // This calculates the quarterly revenue
    Q1Sales = 125000,
    /* This section handles
       customer data processing */
    CustomerData = "processed"
in
    Q1Sales

Case Sensitivity: M is case-sensitive. CustomerName and customername are different variables.

Step References: Each line in the let section creates a step you can reference in subsequent steps:

let
    RawData = Table.FromRows({{"John", 25}, {"Sarah", 32}}, {"Name", "Age"}),
    FilteredData = Table.SelectRows(RawData, each [Age] > 30),
    FinalResult = Table.AddColumn(FilteredData, "Category", each "Senior")
in
    FinalResult

Notice how FilteredData uses RawData, and FinalResult uses FilteredData. This creates a transformation pipeline where each step builds on the previous ones.

Core Data Types in M

M Language supports several fundamental data types. Understanding these types is crucial because M functions expect specific types as inputs and return specific types as outputs.

Primitive Types

Number: Represents both integers and decimals. M automatically handles the precision:

let
    Revenue = 125000,
    TaxRate = 0.08,
    TaxAmount = Revenue * TaxRate
in
    TaxAmount // Returns 10000

Text: String values enclosed in double quotes. Use the ampersand (&) for concatenation:

let
    FirstName = "John",
    LastName = "Smith",
    FullName = FirstName & " " & LastName
in
    FullName // Returns "John Smith"

Logical: Boolean values true or false, often used in filtering and conditional logic:

let
    IsVipCustomer = true,
    HasDiscount = IsVipCustomer and OrderTotal > 1000
in
    HasDiscount

Date, DateTime, and Time: Specific formats for temporal data:

let
    OrderDate = #date(2024, 3, 15),
    OrderDateTime = #datetime(2024, 3, 15, 14, 30, 0),
    OrderTime = #time(14, 30, 0)
in
    OrderDate

Null: Represents missing or unknown values. This is different from empty text or zero:

let
    MissingValue = null,
    EmptyText = "",
    Zero = 0
in
    MissingValue

Complex Types

List: An ordered collection of values, similar to an array in other languages:

let
    ProductCategories = {"Electronics", "Clothing", "Books"},
    Numbers = {1, 2, 3, 4, 5},
    MixedList = {"Text", 123, true, null}
in
    ProductCategories

Record: A collection of name-value pairs, like a single row of data:

let
    Customer = [Name = "Acme Corp", City = "Seattle", Revenue = 125000],
    ProductInfo = [ID = 1001, Name = "Laptop", Price = 899.99]
in
    Customer

Access record fields using square brackets:

let
    Customer = [Name = "Acme Corp", City = "Seattle", Revenue = 125000],
    CustomerName = Customer[Name],
    CustomerRevenue = Customer[Revenue]
in
    CustomerName // Returns "Acme Corp"

Table: A structured collection of rows and columns, the primary data structure in Power Query:

let
    SalesData = Table.FromRows(
        {
            {"Q1", 125000},
            {"Q2", 148000},
            {"Q3", 132000},
            {"Q4", 167000}
        },
        {"Quarter", "Revenue"}
    )
in
    SalesData

Function: Yes, functions are a data type in M! You can assign functions to variables:

let
    CalculateTax = (revenue as number) as number => revenue * 0.08,
    OrderRevenue = 1000,
    TaxOwed = CalculateTax(OrderRevenue)
in
    TaxOwed // Returns 80

Working with Expressions

Expressions are combinations of values, operators, and function calls that evaluate to a result. Understanding how to construct expressions is essential for data transformation.

Arithmetic Expressions

M supports standard mathematical operations:

let
    BasePrice = 100,
    Quantity = 5,
    TaxRate = 0.08,
    Subtotal = BasePrice * Quantity,
    Tax = Subtotal * TaxRate,
    Total = Subtotal + Tax
in
    Total // Returns 540

Operator precedence follows mathematical rules. Use parentheses for clarity:

let
    Result1 = 10 + 5 * 2,    // Returns 20 (multiplication first)
    Result2 = (10 + 5) * 2   // Returns 30 (parentheses first)
in
    Result2

Comparison Expressions

Comparison operators return logical values:

let
    OrderTotal = 1500,
    IsLargeOrder = OrderTotal > 1000,
    IsExactAmount = OrderTotal = 1500,
    IsInRange = OrderTotal >= 1000 and OrderTotal <= 2000
in
    IsInRange // Returns true

Conditional Expressions

The if...then...else expression allows conditional logic:

let
    OrderAmount = 1200,
    DiscountRate = if OrderAmount > 1000 then 0.10 else 0.05,
    Discount = OrderAmount * DiscountRate
in
    Discount // Returns 120

For more complex conditions, you can nest expressions:

let
    CustomerType = "Premium",
    OrderAmount = 800,
    DiscountRate = if CustomerType = "Premium" then
                      if OrderAmount > 1000 then 0.15 else 0.10
                   else
                      if OrderAmount > 1000 then 0.08 else 0.05
in
    DiscountRate // Returns 0.10

The "each" Keyword

The each keyword is M's way of creating inline functions, particularly useful when working with tables and lists. It's equivalent to (_) => where _ represents the current item:

let
    Numbers = {1, 2, 3, 4, 5},
    Doubled = List.Transform(Numbers, each _ * 2)
in
    Doubled // Returns {2, 4, 6, 8, 10}

When working with tables, each operates on the current row:

let
    SalesData = Table.FromRows({{"John", 1000}, {"Sarah", 1500}}, {"Name", "Sales"}),
    WithBonus = Table.AddColumn(SalesData, "Bonus", each [Sales] * 0.1)
in
    WithBonus

In this example, each [Sales] * 0.1 means "for each row, take the Sales column value and multiply by 0.1."

Error Handling

M provides the try...otherwise construct for error handling:

let
    RiskyCalculation = try 10 / 0 otherwise "Error occurred",
    SafeResult = if RiskyCalculation = "Error occurred" then 0 else RiskyCalculation
in
    SafeResult // Returns 0

You can also check for specific error conditions:

let
    TestValue = "abc",
    NumberResult = try Number.FromText(TestValue) otherwise null,
    IsValidNumber = NumberResult <> null
in
    IsValidNumber // Returns false

Function Calls and Parameters

Functions in M are called using parentheses with parameters separated by commas. Understanding function syntax is crucial since most M operations are function calls.

Basic Function Syntax

let
    TextValue = "  Hello World  ",
    Trimmed = Text.Trim(TextValue),
    UpperCase = Text.Upper(Trimmed),
    Length = Text.Length(UpperCase)
in
    Length // Returns 11

Functions with Multiple Parameters

Many functions accept multiple parameters:

let
    OriginalText = "Hello World",
    ReplacedText = Text.Replace(OriginalText, "World", "Universe"),
    ExtractedText = Text.Middle(OriginalText, 6, 5)
in
    ExtractedText // Returns "World"

Optional Parameters

Some functions have optional parameters. When omitted, default values are used:

let
    NumberText = "123.456",
    AsInteger = Number.FromText(NumberText),        // Uses default format
    AsCurrency = Number.FromText(NumberText, "en-US")  // Specifies culture
in
    AsInteger // Returns 123.456

Named Parameters

For functions with many parameters, you can use named parameters for clarity:

let
    SalesTable = Table.FromRows({{"Q1", 1000}, {"Q2", 1200}}, {"Quarter", "Sales"}),
    FilteredTable = Table.SelectRows(
        SalesTable, 
        each [Sales] > 1000
    )
in
    FilteredTable

Hands-On Exercise

Let's apply these concepts to a realistic scenario. Imagine you're processing a customer order dataset with inconsistent formatting. Create a new blank query and enter this M code:

let
    // Raw customer data with formatting issues
    RawData = Table.FromRows(
        {
            {"john.smith@email.com", "  PREMIUM  ", "1,250.00", "2024-03-15"},
            {"sarah.jones@email.com", "standard", "850", "2024/03/16"},
            {"mike.wilson@email.com", "PREMIUM", "2,100.50", "3/17/2024"},
            {"lisa.brown@email.com", "  Standard  ", "500.0", "2024-03-18"}
        },
        {"Email", "CustomerType", "OrderValue", "OrderDate"}
    ),
    
    // Clean the customer type field
    CleanCustomerType = Table.TransformColumns(
        RawData,
        {"CustomerType", each Text.Proper(Text.Trim(_))}
    ),
    
    // Convert order value to numbers (removing commas and handling decimals)
    CleanOrderValue = Table.TransformColumns(
        CleanCustomerType,
        {"OrderValue", each Number.FromText(Text.Replace(_, ",", ""))}
    ),
    
    // Standardize date format
    CleanOrderDate = Table.TransformColumns(
        CleanOrderValue,
        {"OrderDate", each Date.FromText(_)}
    ),
    
    // Add calculated columns
    WithDiscountRate = Table.AddColumn(
        CleanOrderDate,
        "DiscountRate",
        each if [CustomerType] = "Premium" then 0.15 else 0.10
    ),
    
    WithDiscountAmount = Table.AddColumn(
        WithDiscountRate,
        "DiscountAmount",
        each [OrderValue] * [DiscountRate]
    ),
    
    WithFinalTotal = Table.AddColumn(
        WithDiscountAmount,
        "FinalTotal",
        each [OrderValue] - [DiscountAmount]
    )
in
    WithFinalTotal

This exercise demonstrates several key concepts:

1. Data type conversion: Converting text to numbers and dates
2. Text manipulation: Trimming whitespace, changing case, replacing characters
3. Conditional logic: Different discount rates based on customer type
4. Function chaining: Each transformation builds on the previous step
5. Column references: Using [ColumnName] to reference other columns in calculations

Try this: Modify the code to add a "Rush Order" column that shows true if the order value is greater than $2000, false otherwise.

Common Mistakes & Troubleshooting

Type Conversion Errors

Problem: You get an error like "Expression.Error: We cannot convert the value 1250 to type Text."

Solution: M is strict about data types. Use conversion functions explicitly:

// Wrong
let Result = "Order #" & 1250 in Result

// Correct  
let Result = "Order #" & Text.From(1250) in Result

Missing Commas or Syntax Errors

Problem: "Expression.Error: Token '=' expected" or similar parsing errors.

Solution: Check that every line in the let section ends with a comma, except the last one:

// Wrong - missing comma after first line
let
    Name = "John"
    Age = 25
in
    Name

// Correct
let
    Name = "John",
    Age = 25
in
    Name

Null Value Handling

Problem: Your calculations return null unexpectedly.

Solution: Handle null values explicitly:

let
    OrderValue = null,
    // This returns null
    Tax1 = OrderValue * 0.08,
    // This handles null safely
    Tax2 = if OrderValue = null then 0 else OrderValue * 0.08
in
    Tax2 // Returns 0

Case Sensitivity Issues

Problem: "Expression.Error: The name 'customername' wasn't recognized."

Solution: Remember that variable names are case-sensitive:

let
    CustomerName = "Acme Corp",
    // Wrong case - will cause error
    Result1 = customername,
    // Correct case
    Result2 = CustomerName
in
    Result2

Table Column Reference Errors

Problem: "Expression.Error: The field 'sales' of the record wasn't found."

Solution: Column names in table references must match exactly:

let
    SalesData = Table.FromRows({{"Q1", 1000}}, {"Quarter", "Revenue"}),
    // Wrong - column is named "Revenue", not "Sales"
    Total1 = Table.AddColumn(SalesData, "Tax", each [Sales] * 0.08),
    // Correct
    Total2 = Table.AddColumn(SalesData, "Tax", each [Revenue] * 0.08)
in
    Total2

Summary & Next Steps

You've now learned the foundation of M Language: how to structure queries with the let...in pattern, work with M's data types from simple numbers to complex tables, and construct expressions that transform your data. You understand how functions work, how to handle different data types, and how to avoid common syntax errors.

The key concepts to remember:

• Structure: Every M query follows the let...in pattern
• Types: M has specific data types, and conversions must be explicit
• Expressions: Combine values, operators, and functions to transform data
• Functions: Most operations in M are function calls with specific parameter requirements
• Error handling: Use try...otherwise and null checks for robust code

These fundamentals prepare you for more advanced M Language topics like custom functions, advanced table operations, and complex data transformations. In the next lesson, you'll learn how to create reusable custom functions that can handle complex business logic and make your queries more maintainable.

Practice by opening existing Power Query queries and examining their M code in the Advanced Editor. Try to identify the patterns you've learned: variable assignments, function calls, data type conversions, and conditional logic. The more you read and write M code, the more natural it becomes.