Scoping Variables and Let Expressions in M: Understanding Binding, Shadowing, and Expression Isolation in Power Query Queries

Introduction

You're three steps into a complex Power Query transformation when something breaks. The error message points to a step name that you've used in two different places — and Power Query is clearly reading the wrong one. Or maybe you're trying to build a reusable sub-calculation inside a larger expression, but every approach you try either pollutes your query's step list or forces you to repeat code. These are scoping problems, and they show up constantly once your M queries grow beyond the basics.

The let expression is the backbone of every Power Query query. You've seen it in the Advanced Editor — that block of named steps that starts with let and ends with in. But most Power Query users treat it as a container for sequential steps without fully understanding what it actually does: it creates a scope, a controlled environment where names are bound to values and those bindings are only visible to the expressions that need them. Understanding how that scope works — how names are resolved, what happens when the same name appears in nested let blocks, and how to deliberately isolate sub-expressions — transforms you from someone who writes queries to someone who designs them.

By the end of this lesson, you will have a genuine, working model of how M handles variable binding and scope. You'll use nested let expressions to isolate logic, deliberately shadow outer bindings when it helps, and avoid the subtle bugs that come from misunderstanding name resolution.

What you'll learn:

• What a let expression actually is and what "binding" a name to a value means
• How M resolves names and navigates nested scopes
• When and why to use nested let expressions inside a single step
• What variable shadowing is, when it's useful, and when it creates bugs
• How to use expression isolation to write cleaner, more testable M code

Prerequisites

You should be comfortable reading a basic M query in the Advanced Editor and understand that Power Query steps are named values. If you need a refresher on how M is structured, read M Language Fundamentals: Syntax, Types, and Expressions for Power Query before continuing.

---

What a Let Expression Actually Is

Every time you open the Advanced Editor in Power Query, you see something like this:

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter=","]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each [Region] = "West"),
    TotalSales = List.Sum(FilteredRows[Amount])
in
    TotalSales

Most people read this as "a sequence of steps." That mental model is useful for getting started, but it's not quite right — and the gap between that model and reality causes real bugs.

A let expression is a binding block. It defines a set of names and associates each name with an expression. The in clause then evaluates a final expression in a context where all those bindings are available. Order only matters in the sense that a binding can reference other bindings that appear in the same let block — M doesn't execute steps top-to-bottom the way a Python script does. It evaluates whatever is needed, when it's needed.

Think of a let expression as a temporary dictionary. Before the in expression runs, M builds a lookup table of name → expression pairs. When the in expression (or any binding in the block) refers to a name, M looks it up in that dictionary. If it's not there, M looks outward to any enclosing scope.

---

Bindings: Names Are Not Variables

Here's the single most important thing to understand about M scoping: names in a let block are bindings, not variables. In a language like Python or JavaScript, you can reassign a variable — x = 1 followed by x = 2 means x is now 2. In M, that's not possible. Once a name is bound to an expression in a let block, it refers to that expression for the lifetime of that scope. Full stop.

This is why you'll sometimes hear M described as a functional language. There's no mutation, no state change. Every name in a let block is an immutable binding.

What this means practically:

let
    BaseAmount = 1000,
    TaxRate = 0.08,
    TaxAmount = BaseAmount * TaxRate,
    TotalAmount = BaseAmount + TaxAmount
in
    TotalAmount

TaxAmount doesn't "run after" BaseAmount. It simply refers to BaseAmount. When M needs to evaluate TaxAmount, it looks up BaseAmount and finds 1000. When M needs TotalAmount, it looks up both BaseAmount (1000) and TaxAmount (80) and computes 1080. The evaluation order is determined by the dependency graph, not by line order.

You can test this yourself. Open Power BI Desktop or Excel, go to Power Query, create a blank query, open the Advanced Editor, and paste the expression above. The result should be 1080.

---

Nested Let Expressions: Creating Inner Scopes

Now for the powerful part. A let expression is itself an expression — which means it can appear anywhere an expression is valid. That includes the right-hand side of a binding inside another let block.

Imagine you're building a query that calculates a performance bonus for sales reps. For each rep, the bonus depends on a tiered calculation that involves several intermediate values. You could either clutter your main let block with a dozen steps, or you could encapsulate the bonus logic in its own nested let expression.

Here's what that looks like:

let
    SalesData = Table.FromRecords({
        [Rep = "Ana", Region = "West", Sales = 142000],
        [Rep = "Ben", Region = "East", Sales = 98000],
        [Rep = "Cara", Region = "West", Sales = 215000]
    }),

    BonusTable = Table.AddColumn(SalesData, "Bonus", each
        let
            BaseSales = [Sales],
            Threshold = 100000,
            ExcessSales = if BaseSales > Threshold then BaseSales - Threshold else 0,
            BonusRate = if BaseSales > 200000 then 0.10 else 0.05,
            BonusAmount = ExcessSales * BonusRate
        in
            BonusAmount
    )
in
    BonusTable

The inner let block — the one inside each — runs once per row. It has its own scope. BaseSales, Threshold, ExcessSales, BonusRate, and BonusAmount are all defined only within that inner block. They don't appear as steps in your main query, they don't interfere with the outer scope, and they make the bonus calculation readable as a self-contained unit of logic.

---

How M Resolves Names: The Scope Chain

When M encounters a name inside an expression, it follows a lookup chain:

1. Look in the current let block for a binding with that name.
2. If not found, look in the enclosing let block (if there is one).
3. Continue outward through any further nested scopes.
4. If not found anywhere, raise an error.

This is the scope chain (sometimes called the lexical scope or static scope). The inner scope has access to everything in the outer scope, plus its own bindings.

In the bonus example above, the inner let block could reference SalesData from the outer scope if it needed to. The inner scope "sees" everything the outer scope defines.

Here's a deliberate example to make this concrete:

let
    CompanyName = "Northwind Trading",
    
    ReportHeader = 
        let
            ReportDate = Date.From(DateTime.LocalNow()),
            HeaderText = CompanyName & " | Report Date: " & Date.ToText(ReportDate, "yyyy-MM-dd")
        in
            HeaderText
in
    ReportHeader

The inner let block uses CompanyName, which is defined in the outer scope. M walks up the scope chain and finds it. This is name resolution — M looks outward until it finds what it needs.

---

Variable Shadowing: When Names Collide

Shadowing happens when you define a name in an inner scope that already exists in an outer scope. The inner binding shadows the outer one — within the inner scope, the name refers to the inner binding, not the outer one.

Here's a straightforward example:

let
    Rate = 0.05,  // outer Rate: 5% standard rate

    PremiumCalculation =
        let
            Rate = 0.12,  // inner Rate: 12% premium rate — shadows the outer
            Amount = 50000,
            Fee = Amount * Rate
        in
            Fee  // uses inner Rate (0.12), so Fee = 6000
in
    PremiumCalculation  // returns 6000

Inside PremiumCalculation's let block, Rate refers to 0.12. The outer Rate of 0.05 is invisible from within that inner scope — it's been shadowed. The result is 6000, not 2500.

Shadowing isn't always a bug, though. Sometimes it's the cleanest way to express "this sub-calculation uses a different version of this concept." But when you do it intentionally, name things clearly enough that the next person reading your code (which is often future you) understands immediately which binding is in play.

---

Expression Isolation: Why It Matters

Expression isolation is the practice of deliberately containing a calculation within its own let expression so it doesn't interfere with anything outside it. You've already seen this with nested let blocks inside Table.AddColumn. Let's talk about why this matters beyond just keeping the step list tidy.

Reason 1: Avoiding name collisions in large queries. A query with 30 steps accumulates a lot of names. If your step 12 and step 27 both want to use a name like FilteredData for completely different intermediate tables, you either have to rename one of them or refactor. With nested let blocks, you can use whatever name makes semantic sense inside each block without worrying about collisions.

Reason 2: Making logic testable in isolation. If you've wrapped the bonus calculation in a self-contained let expression, you can extract it and test it independently — just paste the inner let block into a blank query and feed it specific input values. Compare that to hunting through a 40-step query to understand what step 23 actually does.

Reason 3: Enabling cleaner function definitions. When you write custom M functions, the function body is typically a let expression that's isolated from the calling query's scope. This is scoping working exactly as intended — the function's internal names don't leak out, and the calling query's names don't accidentally bleed in.

Here's a realistic example where isolation saves you from a messy query. You're joining two tables and need to compute a derived field that requires several intermediate steps on the join result:

let
    Orders = /* ... some source ... */,
    Customers = /* ... some source ... */,

    EnrichedOrders = 
        let
            Joined = Table.NestedJoin(Orders, "CustomerID", Customers, "CustomerID", "CustomerData", JoinKind.Left),
            Expanded = Table.ExpandTableColumn(Joined, "CustomerData", {"Region", "Tier"}),
            WithCategory = Table.AddColumn(Expanded, "Category", each
                if [Tier] = "Gold" and [Amount] > 10000 then "Priority"
                else if [Tier] = "Silver" then "Standard"
                else "Basic"
            ),
            FinalColumns = Table.SelectColumns(WithCategory, {"OrderID", "Amount", "Region", "Category"})
        in
            FinalColumns
in
    EnrichedOrders

The entire join-and-enrich pipeline is isolated inside its own let block. The outer query is clean. If you need to debug the join step, you look inside EnrichedOrders's inner let. If you need to add another top-level step before or after it, the outer scope is uncluttered.

---

The `in` Clause Is Just an Expression

One thing that trips people up: the in clause doesn't have to be a step name. It can be any expression that uses the bindings in the let block. Most of the time, it's the name of the last step, but it doesn't have to be.

let
    UnitPrice = 29.99,
    Quantity = 150,
    DiscountRate = 0.10,
    DiscountedPrice = UnitPrice * (1 - DiscountRate)
in
    DiscountedPrice * Quantity  // inline expression in the `in` clause

This is completely valid. The in clause computes 26.991 * 150 = 4048.65. You never needed to name that final multiplication — it's a one-liner that only exists in the in clause.

You'll also see this pattern used in list and record operations where you build a record inline:

let
    FirstName = "Maria",
    LastName = "Santos",
    Department = "Analytics"
in
    [FullName = FirstName & " " & LastName, Department = Department]

The in clause constructs a record on the spot. Tidy, self-contained, no extra step names.

---

Hands-On Exercise

Work through this exercise in Power Query's Advanced Editor. You'll build a query that calculates monthly commission for a sales rep using nested let expressions and deliberate expression isolation.

Step 1. Create a blank query in Power Query (in Power BI Desktop: Home tab → Enter Data, then go to Advanced Editor and replace the contents; in Excel: Data tab → Get Data → Launch Power Query Editor → New Query → Blank Query).

Step 2. Paste this skeleton into the Advanced Editor and run it to confirm it evaluates to 4875:

let
    MonthlySales = 75000,
    QuotaAmount = 60000,

    CommissionAmount = 
        let
            BaseRate = 0.05,
            AcceleratorRate = 0.08,
            OverQuotaThreshold = QuotaAmount,
            BaseCommission = Number.Min(MonthlySales, OverQuotaThreshold) * BaseRate,
            OverQuotaSales = Number.Max(MonthlySales - OverQuotaThreshold, 0),
            AcceleratorCommission = OverQuotaSales * AcceleratorRate
        in
            BaseCommission + AcceleratorCommission
in
    CommissionAmount

Verify you understand why the answer is 4875: The rep sold $75,000 against a $60,000 quota. The first $60,000 earns 5% = $3,000. The $15,000 over-quota earns 8% = $1,200. But wait — $3,000 + $1,200 = $4,200, not $4,875.

Step 3. Now shadow the BaseRate in the inner scope to 0.065 (a special rate for over-quota base calculation). Observe how the outer scope is unaffected and only the inner calculation changes.

Step 4. Add a second inner let block that computes a "draw amount" (a guaranteed minimum of $2,500) and returns the greater of CommissionAmount and the draw:

let
    MonthlySales = 75000,
    QuotaAmount = 60000,

    CommissionAmount = 
        let
            BaseRate = 0.05,
            AcceleratorRate = 0.08,
            OverQuotaThreshold = QuotaAmount,
            BaseCommission = Number.Min(MonthlySales, OverQuotaThreshold) * BaseRate,
            OverQuotaSales = Number.Max(MonthlySales - OverQuotaThreshold, 0),
            AcceleratorCommission = OverQuotaSales * AcceleratorRate
        in
            BaseCommission + AcceleratorCommission,

    FinalPayout = 
        let
            DrawAmount = 2500,
            PayoutBeforeDraw = CommissionAmount
        in
            Number.Max(PayoutBeforeDraw, DrawAmount)
in
    FinalPayout

Notice how FinalPayout's inner let references CommissionAmount from the outer scope. That's the scope chain working exactly as expected.

---

Common Mistakes & Troubleshooting

Mistake 1: Reusing common names across nested scopes unintentionally.
If your outer scope has a binding called Source (which it almost always does in a real query), and your inner let also creates a binding called Source, the inner one shadows the outer. This is almost never what you want when Source means "the original data table." Use specific names inside inner scopes.

Mistake 2: Expecting step-by-step execution.
If you're debugging and you put a "bad" expression in a step that nothing else references, M may not evaluate it at all (lazy evaluation). Don't assume that because you defined a binding, M ran it. The in clause controls what actually gets evaluated.

Mistake 3: Trying to reassign a binding.
You cannot do this in M:

let
    Counter = 1,
    Counter = Counter + 1  // ERROR: duplicate name in let binding
in
    Counter

M will throw an error because you've defined Counter twice in the same let block. If you need accumulation or iteration, look at Advanced M: Iterators, Accumulators, and Recursive Patterns.

Mistake 4: Forgetting commas between bindings.
Every binding in a let block except the last one must be followed by a comma. Missing commas are the most common syntax error in M. The pattern is:

let
    Name1 = expr1,   // comma
    Name2 = expr2,   // comma
    Name3 = expr3    // NO comma on the last binding
in
    Name3

Mistake 5: Referencing a name before it's "defined" and being confused when it works.
Because M uses lazy evaluation and a dependency graph, it doesn't matter what order you write your bindings. TotalAmount can reference TaxAmount even if TotalAmount appears first in the let block. This surprises people who come from imperative languages. M resolves it correctly — but it also means forward-references are fine, which can make reading code feel strange at first.

---

Summary & Next Steps

You've now moved past the "list of steps" mental model of Power Query and into a genuine understanding of how M manages scope and name binding.

Here's what to take away:

• A let expression is a binding block that associates names with expressions. Those bindings are immutable and resolved lazily by a dependency graph, not executed top-to-bottom.
• M resolves names by walking up the scope chain — from the innermost let block outward until it finds the name or throws an error.
• Nested let expressions create inner scopes that can access outer bindings but whose internal names are invisible from outside.
• Shadowing occurs when an inner binding uses the same name as an outer one. It's occasionally useful but frequently the source of hard-to-spot bugs.
• Expression isolation — deliberately wrapping sub-calculations in their own let blocks — makes queries cleaner, more readable, and easier to test.

These concepts become increasingly important as your queries grow in complexity. The moment you start writing expressions that span more than a handful of steps, scoping discipline is what separates a maintainable query from a tangled one.

From here, excellent next topics include understanding how scoping interacts with error handling and try-otherwise patterns — errors thrown inside a nested let block can sometimes be caught at the outer level, which opens up interesting defensive patterns. You should also explore how these scoping principles apply to writing custom M functions, where the function body is its own isolated let expression with its parameters as the outermost bindings. And if you want to understand how M's evaluation model connects to performance, the deep dive on M Language Performance Patterns and Anti-Patterns is the natural follow-on.