Implementing Custom Table.Buffer and In-Memory Caching Strategies in M to Eliminate Redundant Query Recalculations

Introduction

You've built a Power Query transformation that works — but it's painfully slow. You open the Query Editor, make a small change, and then wait. And wait. You notice the status bar cycling through the same data source connection multiple times, the same filtering steps appearing in the evaluation log over and over. What's happening is that Power Query's evaluation engine is recalculating the same intermediate results repeatedly because nothing is telling it to hold those results in memory between uses. Every reference to the same table triggers a fresh evaluation, and if that table is sourced from a slow database, a REST API, or a multi-step transformation chain, you pay that cost every single time.

This problem compounds badly in realistic data models. When you have a facts table that joins against a slowly-loaded lookup table, and that lookup table is referenced in three different let bindings or across multiple queries, you can easily end up hitting your data source five to ten times for what should be one read. Table.Buffer exists precisely to break this cycle — but it's widely misunderstood, inconsistently applied, and often used in ways that either don't help or actively make things worse. Understanding when and why buffering works requires understanding how the M evaluation engine thinks about laziness, referential transparency, and step identity.

By the end of this lesson, you'll be able to diagnose redundant evaluation problems, apply Table.Buffer (and its siblings List.Buffer and Binary.Buffer) with precision, design multi-query caching architectures in Power BI Desktop, understand the performance trade-offs that buffering imposes, and avoid the common anti-patterns that make developers think caching is broken when it actually isn't. This is not a beginner topic — we're going to look at internals, edge cases, and real architectural decisions.

What you'll learn:

• How M's lazy evaluation model causes redundant recalculations and why Power Query doesn't automatically cache intermediate results
• The mechanics of Table.Buffer, List.Buffer, and Binary.Buffer — what they actually do under the hood
• Practical patterns for strategic buffering in complex transformation pipelines
• How to design shared in-memory caching across multiple queries using Power BI's query dependency model
• How to measure whether buffering is helping using the Diagnostics API and Query Evaluation logs
• Advanced caching strategies including conditional buffering, parameterized cache invalidation, and the limitations of buffering in DirectQuery contexts

---

Prerequisites

This lesson assumes you are comfortable with:

• Writing multi-step let...in expressions in M from scratch
• Understanding Power Query's folding model and what happens when folding breaks
• Basic familiarity with Table.TransformColumns, List.Generate, and recursive M patterns
• Power BI Desktop's query dependency view and the difference between Import and DirectQuery modes

If you haven't yet worked through the Advanced M Language path's lessons on lazy evaluation and query folding, read those first. The concepts here build directly on that foundation.

---

How M's Lazy Evaluation Engine Creates Redundant Work

Before you can fix a redundant evaluation problem, you need to understand why it exists. M is a lazy, functional language. When you write a let expression, you are not executing code top to bottom — you are defining a graph of named values. The engine only evaluates a step when its value is actually needed to produce the final output.

This laziness is powerful. It enables query folding — the ability to push transformation logic back to source systems like SQL Server or Salesforce — because the engine can inspect the entire transformation graph before executing anything. But laziness has a cost: the engine doesn't automatically memoize intermediate results.

Consider this M expression:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="Orders"]}[Data],
    FilteredOrders = Table.SelectRows(OrdersTable, each [OrderDate] >= #date(2024, 1, 1)),
    
    TotalRevenue = List.Sum(FilteredOrders[Revenue]),
    OrderCount = Table.RowCount(FilteredOrders),
    AverageOrderValue = TotalRevenue / OrderCount
in
    AverageOrderValue

This looks like three calculations on FilteredOrders. You might expect FilteredOrders to be evaluated once, with TotalRevenue and OrderCount both reading from a held-in-memory result. But the engine may evaluate FilteredOrders — and by extension OrdersTable and Source — multiple times, once for each dependent step, especially when the query can't be fully folded or when an upstream step involves a data connector that doesn't support streaming.

The engine's behavior here depends on several factors: whether the expression is referentially transparent (no side effects, same inputs always produce same outputs), whether the connector implements result caching at the connector level, and whether the evaluation context has been interrupted by a step that breaks folding. In practice, with most real-world transformations that include custom M steps, you will see multiple evaluations of the same logical computation.

Key insight: M's lack of automatic memoization is a deliberate design choice to support query folding. Memoizing intermediate results would require materializing them, which destroys the ability to push predicate filters down to source systems. You are always trading off between folding efficiency and memory caching, and Table.Buffer is the explicit mechanism for making that trade-off conscious and intentional.

---

Understanding Table.Buffer: What It Actually Does

Table.Buffer takes a table as input and returns a table whose rows are fully loaded into memory before any downstream consumer reads from it. The function signature is simple:

Table.Buffer(table as table, optional options as nullable record) as table

But the behavior is subtle. When you call Table.Buffer(someTable), you are making a promise to the engine: evaluate someTable completely, store all of its rows in the evaluation context's memory, and then serve all subsequent reads of the result from that in-memory copy. No re-evaluation. No re-querying the source.

The critical word is "completely." Table.Buffer forces eager evaluation. This means it breaks query folding for everything downstream of it. If you buffer a table that could have had its WHERE clause pushed to SQL Server, you've just pulled all rows into Power Query's process and filtered them in memory instead. That's sometimes exactly what you want. It's never something you should do accidentally.

Here's the pattern that most people write when they first learn about Table.Buffer:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="Orders"]}[Data],
    FilteredOrders = Table.SelectRows(OrdersTable, each [OrderDate] >= #date(2024, 1, 1)),
    BufferedOrders = Table.Buffer(FilteredOrders),
    
    TotalRevenue = List.Sum(BufferedOrders[Revenue]),
    OrderCount = Table.RowCount(BufferedOrders),
    AverageOrderValue = TotalRevenue / OrderCount
in
    AverageOrderValue

Now FilteredOrders is evaluated once, fully materialized, and TotalRevenue and OrderCount both read from BufferedOrders in memory. The database query runs exactly once.

But notice what we've given up: any filter folding that might have happened for TotalRevenue or OrderCount downstream is now impossible because the data is already in memory. For a table with hundreds of millions of rows, buffering before filtering would be catastrophic. The buffering point must always come after the last foldable operation that reduces your dataset.

Warning: Never buffer before filtering when the source supports query folding. The correct pattern is: filter first (let folding push predicates to the source), then buffer the reduced result set. Buffering the full Orders table and filtering in memory is almost always wrong.

---

The Siblings: List.Buffer and Binary.Buffer

Table.Buffer gets most of the attention, but its siblings matter in real scenarios.

List.Buffer does the same thing for lists. This matters more than you might think because lists are the backbone of many M computation patterns:

let
    Source = Csv.Document(File.Contents("C:\Data\products.csv"), [Delimiter=",", Columns=4]),
    ProductTable = Table.PromoteHeaders(Source),
    
    // This list might be re-evaluated multiple times if used in lookups
    ProductIDs = ProductTable[ProductID],
    BufferedIDs = List.Buffer(ProductIDs),
    
    // Now use BufferedIDs in multiple List.Contains checks without re-reading the file
    OrderSource = Sql.Database("prod-server", "SalesDB"),
    Orders = OrderSource{[Schema="dbo", Item="Orders"]}[Data],
    FilteredOrders = Table.SelectRows(Orders, each List.Contains(BufferedIDs, [ProductID]))
in
    FilteredOrders

The List.Contains call inside Table.SelectRows is evaluated for every row of Orders. Without List.Buffer, each evaluation might trigger a re-read of the CSV file. With it, the list is in memory and each lookup is a fast in-memory operation.

Binary.Buffer is useful when you're working with file content directly — reading a file, processing it in multiple ways, and wanting to avoid multiple file system reads:

let
    RawBinary = File.Contents("C:\Data\large_export.json"),
    BufferedBinary = Binary.Buffer(RawBinary),
    
    AsJson = Json.Document(BufferedBinary),
    AsText = Text.FromBinary(BufferedBinary)
in
    // Both AsJson and AsText read from memory, not disk
    AsJson

---

Diagnosing Redundant Evaluations Before Buffering

Applying Table.Buffer blindly is an anti-pattern. You should measure before you optimize. Power Query provides two tools for this: Query Diagnostics and the Evaluation Traces.

To enable Query Diagnostics in Power BI Desktop, navigate to the Power Query Editor (Home tab, then Transform Data), then go to the Tools menu and select Start Diagnostics. Run your query by refreshing, then stop diagnostics. You'll get two tables: a summary table and a detailed trace table.

In the detailed trace table, look for the Activity column and filter for OData.Feed, Sql.Database, Web.Contents, or whatever your source connector is. If you see the same logical query appearing multiple times — same source, same parameters — that's your smoking gun. Count the occurrences. That's how many times the engine evaluated that source step.

For more granular analysis, you can add explicit Diagnostics.Trace calls to your M code during debugging:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="Orders"]}[Data],
    
    // Log when this step is evaluated
    TracedOrders = let _ = Diagnostics.Trace(TraceLevel.Information, "OrdersTable evaluated", OrdersTable, true) in _,
    
    FilteredOrders = Table.SelectRows(TracedOrders, each [OrderDate] >= #date(2024, 1, 1)),
    
    TotalRevenue = List.Sum(FilteredOrders[Revenue]),
    OrderCount = Table.RowCount(FilteredOrders)
in
    TotalRevenue + OrderCount

Note: Diagnostics.Trace is not available in all Power Query hosts. It works in Power BI Desktop with diagnostics enabled. In Power Query for Excel, you may need to rely solely on the built-in diagnostics tables.

When you check the trace output, each appearance of "OrdersTable evaluated" in the log corresponds to one actual evaluation of that step. If you see it twice, you need buffering.

---

Strategic Buffering Patterns for Complex Pipelines

Pattern 1: The Shared Lookup Table

This is the most common scenario where buffering delivers dramatic, measurable improvements. You have a lookup table — say, a customer dimension — that is joined against multiple fact tables. Without buffering, each join triggers a fresh evaluation of the lookup table query.

// Query: CustomerDimension (shared, buffered)
let
    Source = Sql.Database("prod-server", "DW"),
    RawCustomers = Source{[Schema="dw", Item="DimCustomer"]}[Data],
    
    // Apply all transformations while folding is still possible
    ActiveCustomers = Table.SelectRows(RawCustomers, each [IsActive] = true),
    SelectedColumns = Table.SelectColumns(ActiveCustomers, 
        {"CustomerKey", "CustomerName", "Region", "Segment", "AnnualRevenueBand"}),
    
    // Buffer AFTER the foldable operations have reduced the dataset
    Buffered = Table.Buffer(SelectedColumns)
in
    Buffered

Now in your fact table queries, you reference CustomerDimension directly:

// Query: SalesAnalysis
let
    Source = Sql.Database("prod-server", "DW"),
    FactSales = Source{[Schema="dw", Item="FactSales"]}[Data],
    
    JoinedData = Table.NestedJoin(FactSales, {"CustomerKey"}, 
        CustomerDimension, {"CustomerKey"}, "CustomerInfo", JoinKind.Left),
    
    Expanded = Table.ExpandTableColumn(JoinedData, "CustomerInfo", 
        {"CustomerName", "Region", "Segment"})
in
    Expanded

Because CustomerDimension is buffered in its own query, any query that references it reads from an already-materialized in-memory table. The database isn't hit again.

Important caveat: This cross-query buffering only works reliably within a single Power BI Desktop refresh session. Each time you click Refresh All, the entire evaluation graph restarts. The buffer lives for the duration of a refresh operation, not permanently. This distinction matters for understanding when your caching strategy will and won't help.

Pattern 2: Recursive Computations That Reference Their Own Intermediate State

Recursive M patterns are notorious for triggering redundant evaluations. Consider a running total calculation:

// Naive approach - extremely slow on large tables
let
    Source = Sql.Database("prod-server", "SalesDB"),
    DailySales = Source{[Schema="dbo", Item="DailySalesSummary"]}[Data],
    Sorted = Table.Sort(DailySales, {{"SaleDate", Order.Ascending}}),
    
    SalesAmounts = Sorted[SalesAmount],
    
    RunningTotals = List.Generate(
        () => [total = SalesAmounts{0}, index = 0],
        each [index] < List.Count(SalesAmounts),
        each [total = [total] + SalesAmounts{[index] + 1}, index = [index] + 1],
        each [total]
    )
in
    RunningTotals

The SalesAmounts{[index] + 1} access inside List.Generate re-evaluates SalesAmounts — and through it Sorted, DailySales, and Source — on every iteration. For a table with 365 rows, that's 365 database queries.

The fix is aggressive buffering at the list level:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    DailySales = Source{[Schema="dbo", Item="DailySalesSummary"]}[Data],
    Sorted = Table.Sort(DailySales, {{"SaleDate", Order.Ascending}}),
    BufferedSorted = Table.Buffer(Sorted),  // Buffer the table
    
    SalesAmounts = List.Buffer(BufferedSorted[SalesAmount]),  // Buffer the list
    
    RunningTotals = List.Generate(
        () => [total = SalesAmounts{0}, index = 0],
        each [index] < List.Count(SalesAmounts),
        each [total = [total] + SalesAmounts{[index] + 1}, index = [index] + 1],
        each [total]
    )
in
    RunningTotals

Now SalesAmounts is a fully loaded in-memory list. The 365 index accesses are 365 lookups into a List data structure in memory, not 365 database round-trips.

Pattern 3: Custom Column Functions That Re-Evaluate Their Inputs

When you use Table.AddColumn with a custom function that references an external table, every row of the main table triggers a fresh evaluation of that external table. This is one of the most common and least obvious sources of redundant queries.

// Problematic pattern
let
    Source = Sql.Database("prod-server", "SalesDB"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    
    // PricingTable is re-evaluated for EVERY ROW of Orders
    WithMarkup = Table.AddColumn(Orders, "SuggestedPrice", each 
        let
            PricingSource = Sql.Database("prod-server", "PricingDB"),
            PricingTable = PricingSource{[Schema="dbo", Item="ProductPricing"]}[Data],
            MatchedRow = Table.SelectRows(PricingTable, each [ProductID] = [ProductID])
        in
            if Table.RowCount(MatchedRow) > 0 then MatchedRow[Price]{0} else null
    )
in
    WithMarkup

This is a catastrophe. For 10,000 orders, you're running 10,000 queries against PricingDB. The fix is to pull the lookup outside the row-level function and buffer it:

let
    Source = Sql.Database("prod-server", "SalesDB"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    
    // Load pricing data ONCE, outside the row function
    PricingSource = Sql.Database("prod-server", "PricingDB"),
    PricingTable = PricingSource{[Schema="dbo", Item="ProductPricing"]}[Data],
    BufferedPricing = Table.Buffer(PricingTable),
    
    // Build a lookup record for O(1) access by ProductID
    PricingLookup = Record.FromList(
        BufferedPricing[Price],
        List.Transform(BufferedPricing[ProductID], Text.From)
    ),
    
    // Now the lookup is a simple record field access - extremely fast
    WithMarkup = Table.AddColumn(Orders, "SuggestedPrice", each 
        Record.FieldOrDefault(PricingLookup, Text.From([ProductID]), null)
    )
in
    WithMarkup

This pattern — buffer a lookup table, convert it to a Record for O(1) access, then use it inside the row function — is one of the most important performance patterns in advanced M. The Record.FromList / Record.FieldOrDefault combination gives you hash-map-style lookups, which are dramatically faster than Table.SelectRows inside a row function.

Performance note: Record.FieldOrDefault has O(1) average complexity for record field lookups in recent Power Query engine versions. List.Contains on a buffered list is O(n). For lookup scenarios with more than a few hundred values, prefer the Record approach.

---

Building a Multi-Query Caching Architecture in Power BI Desktop

In real Power BI models with dozens of queries, you need a systematic approach to caching rather than ad-hoc buffering. The recommended pattern is to create dedicated "staging" queries that handle source connections and initial preparation, buffer those, and then build all transformation queries on top of them.

The Layered Query Architecture

Organize your queries into three conceptual layers:

Layer 1 — Raw Connections (no buffering): These queries connect to sources and apply only foldable operations. They should never be loaded into the model directly.

// _Raw_Orders (disabled load, not loaded to model)
let
    Source = Sql.Database("prod-server", "SalesDB"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Only apply filters that can be pushed to SQL
    RecentOrders = Table.SelectRows(Orders, each [OrderDate] >= #date(2023, 1, 1))
in
    RecentOrders

Layer 2 — Staged and Buffered (no direct model load): These queries consume the raw queries, apply any M-level transformations that break folding, and then buffer the result. This is where Table.Buffer lives.

// _Staged_Orders (disabled load, not loaded to model)
let
    Raw = _Raw_Orders,
    // Transformations that break folding go here
    WithParsedDates = Table.TransformColumns(Raw, {
        {"ShipDate", each if _ = null then null else Date.From(Text.Start(_, 10)), type date}
    }),
    WithDerivedColumns = Table.AddColumn(WithParsedDates, "QuarterLabel", 
        each "Q" & Text.From(Date.QuarterOfYear([OrderDate])) & " " & 
             Text.From(Date.Year([OrderDate])), type text),
    Buffered = Table.Buffer(WithDerivedColumns)
in
    Buffered

Layer 3 — Model Tables (loaded to model): These queries consume the staged queries and perform any final shaping. Since their inputs are already buffered, they're fast and won't re-trigger source queries.

// Orders (loaded to model)
let
    Staged = _Staged_Orders,
    FinalColumns = Table.SelectColumns(Staged, {
        "OrderID", "CustomerKey", "ProductKey", "OrderDate", 
        "QuarterLabel", "Revenue", "Quantity", "ShipDate"
    }),
    TypedTable = Table.TransformColumnTypes(FinalColumns, {
        {"OrderID", Int64.Type},
        {"Revenue", Currency.Type},
        {"Quantity", Int64.Type}
    })
in
    TypedTable

This architecture gives you clean separation of concerns, makes it easy to see where folding ends and M processing begins, and ensures that expensive source queries run exactly once regardless of how many model tables consume them.

Naming conventions matter here. Prefix staging queries with underscore (`_Raw_`, `_Staged_`) and configure them as "Enable load: Off" in the Query Properties. This keeps your model clean while preserving the caching benefits.

---

Conditional and Parameterized Buffering

There are scenarios where you want buffering to happen conditionally — for example, only when the dataset is large enough to justify the memory cost, or only in production environments.

// Utility function: buffer only if row count exceeds threshold
let
    SmartBuffer = (tableToBuffer as table, rowCountThreshold as number) as table =>
        let
            RowCount = Table.RowCount(tableToBuffer),
            Result = if RowCount > rowCountThreshold 
                     then Table.Buffer(tableToBuffer)
                     else tableToBuffer
        in
            Result
in
    SmartBuffer

Warning: Be careful with this pattern. Calling Table.RowCount on an unbuffered table may itself trigger a full evaluation. In some cases, this means you evaluate the table once to count rows and then again to buffer it — exactly the redundant evaluation you were trying to avoid. This pattern is most useful when tableToBuffer is already the output of a previous buffer step, making the row count check cheap.

A more practical conditional buffering pattern uses a query parameter to switch between development and production behavior:

// Parameter: IsProductionRefresh (True/False)

// _Staged_CustomerDimension
let
    Source = Sql.Database("prod-server", "DW"),
    Customers = Source{[Schema="dw", Item="DimCustomer"]}[Data],
    Filtered = Table.SelectRows(Customers, each [IsActive] = true),
    
    // In production, buffer. In development, skip buffering for faster iteration
    MaybeBuffered = if IsProductionRefresh then Table.Buffer(Filtered) else Filtered
in
    MaybeBuffered

This lets developers iterate quickly without materializing large tables on every query test, while ensuring production refreshes get the full caching benefit.

---

Memory Management and the Costs of Buffering

Table.Buffer doesn't come free. It trades CPU and I/O costs (repeated evaluations) for memory costs (holding the result set in RAM for the duration of the refresh). For well-sized datasets — say, a few million rows with a handful of columns — this is an excellent trade. For very large tables, it can cause memory pressure that actually slows down the refresh or causes it to fail.

The rough memory calculation for a buffered table is: (average row size in bytes) × (row count). A table with 5 million rows averaging 200 bytes per row will consume roughly 1 GB of RAM while buffered. If you have five such tables buffered simultaneously, you need 5 GB of available RAM in the Power Query process.

In Power BI Desktop, the Power Query evaluation runs in the msmdsrv.exe process (the Analysis Services Tabular engine). This process competes for memory with the model itself during refresh. On a typical developer laptop with 16 GB RAM, having 3–4 GB occupied by buffered staging tables can cause the model load phase to slow significantly.

Practical guidelines:

• Buffer lookup and dimension tables aggressively — they're usually small and provide high leverage.
• Buffer fact tables only after significant row reduction from foldable filters.
• Monitor your machine's RAM usage during a full refresh to identify memory pressure points.
• Consider whether a join can be pushed to the source (via folding) instead of materialized in M.

Architectural principle: Every Table.Buffer call should be a deliberate decision with a known justification. Document it in a comment in your M code — write why you're buffering here, what problem it solves, and approximately how large the buffered result is. This saves enormous debugging time for anyone who inherits the model.

---

Buffering in DirectQuery and Composite Models

If you're thinking "I'll just buffer everything in DirectQuery mode to make it faster," stop. Table.Buffer has no meaningful effect in DirectQuery mode because the M query isn't evaluated during report rendering — the M is used only to define the schema and the initial data source connection. In DirectQuery, every visualization triggers a DAX query that generates a source SQL query. The M transformation layer is bypassed for data retrieval.

In Composite Models (where some tables are Import and some are DirectQuery), buffering applies only to the Import tables' refresh process. The DirectQuery tables are unaffected.

There is one scenario in Composite Models where buffering matters more subtly: when you have a calculated column or measure that references both an Import table and a DirectQuery table. The Import table's staging queries benefit from buffering during the Import refresh phase. But the cross-source evaluation at query time doesn't benefit from M-level buffering — that's a DAX optimization problem, not an M optimization problem.

---

Hands-On Exercise

Let's work through a realistic scenario end-to-end. You're building a financial reporting model for a retail chain. You have:

• A DimProduct table in SQL Server (~50,000 rows) with category, subcategory, brand, and pricing tier
• A FactSales table (~8 million rows, filtered to last 2 years for the model)
• A FactInventory table (~4 million rows) that references the same DimProduct
• A budget vs. actual analysis that needs DimProduct in a third context

The current refresh takes 14 minutes. Query Diagnostics shows DimProduct being queried 7 times.

Step 1: Create the raw connection query

Create a new query named _Raw_DimProduct. Disable its load to the model.

let
    Source = Sql.Database("retail-server", "RetailDW"),
    Products = Source{[Schema="dw", Item="DimProduct"]}[Data],
    // Apply only filters that SQL Server can fold
    ActiveProducts = Table.SelectRows(Products, each [DiscontinuedFlag] = false)
in
    ActiveProducts

Step 2: Create the staged and buffered query

Create a new query named _Staged_DimProduct. Disable its load to the model.

let
    Raw = _Raw_DimProduct,
    
    // These operations break folding - do them in M after the SQL pull
    NormalizedNames = Table.TransformColumns(Raw, {
        {"ProductName", Text.Proper, type text},
        {"BrandName", Text.Upper, type text}
    }),
    
    WithPriceTier = Table.AddColumn(NormalizedNames, "PriceTierLabel",
        each if [RetailPrice] >= 500 then "Premium"
             else if [RetailPrice] >= 100 then "Mid-Range"
             else "Value",
        type text),
    
    // Buffer after all transformations - this is the single source of truth
    Buffered = Table.Buffer(WithPriceTier)
in
    Buffered

Step 3: Build the PricingLookup record for fast row-level access

Create a query named _Lookup_ProductPricing. Disable its load.

let
    Staged = _Staged_DimProduct,
    // Build a Record for O(1) lookup by ProductKey
    PriceRecord = Record.FromList(
        List.Transform(Staged[RetailPrice], each Number.From(_)),
        List.Transform(Staged[ProductKey], each "P" & Text.From(_))
    )
in
    PriceRecord

Step 4: Build the model-facing DimProduct table

// DimProduct (enabled for load)
let
    Staged = _Staged_DimProduct,
    ModelColumns = Table.SelectColumns(Staged, {
        "ProductKey", "ProductName", "BrandName", 
        "Category", "Subcategory", "PriceTierLabel"
    })
in
    ModelColumns

Step 5: Update FactSales to use the buffered lookup

// FactSales (enabled for load)
let
    Source = Sql.Database("retail-server", "RetailDW"),
    Sales = Source{[Schema="dw", Item="FactSales"]}[Data],
    RecentSales = Table.SelectRows(Sales, each [SaleDate] >= #date(2023, 1, 1)),
    
    // Use buffered lookup - no cross-query source hit
    PricingLookup = _Lookup_ProductPricing,
    
    WithCurrentPrice = Table.AddColumn(RecentSales, "CurrentRetailPrice", each
        Record.FieldOrDefault(PricingLookup, "P" & Text.From([ProductKey]), null),
        type number),
    
    TypedSales = Table.TransformColumnTypes(WithCurrentPrice, {
        {"Revenue", Currency.Type},
        {"Quantity", Int64.Type}
    })
in
    TypedSales

After implementing this architecture, run the refresh again with Query Diagnostics enabled. You should see DimProduct's underlying SQL query appearing exactly once — once in _Raw_DimProduct, with all other references served from the buffer.

In practice, this type of refactoring on real-world models with a similar profile typically reduces refresh times by 40–70% for the affected table chain.

---

Common Mistakes and Troubleshooting

Mistake 1: Buffering Before Filtering Breaks Folding Without Benefit

// WRONG - buffers 8 million rows, then filters in M
let
    Source = Sql.Database("prod-server", "DB"),
    FullTable = Source{[Schema="dbo", Item="FactSales"]}[Data],
    Buffered = Table.Buffer(FullTable),  // 8M rows in RAM!
    Filtered = Table.SelectRows(Buffered, each [Year] = 2024)
in
    Filtered

// RIGHT - folds the filter to SQL, then buffers the small result
let
    Source = Sql.Database("prod-server", "DB"),
    FullTable = Source{[Schema="dbo", Item="FactSales"]}[Data],
    Filtered = Table.SelectRows(FullTable, each [Year] = 2024),  // Folded to SQL
    Buffered = Table.Buffer(Filtered)  // Buffer the ~200K row result
in
    Buffered

Mistake 2: Buffering Inside a Function That's Called Per Row

The entire purpose of moving lookups outside row functions is defeated if you put buffering inside the function:

// WRONG - Table.Buffer is called once per row, providing zero benefit
WithMarkup = Table.AddColumn(Orders, "Price", each 
    let
        Pricing = Table.Buffer(  // This buffers a different instance each call
            Sql.Database("server", "db"){[Schema="dbo", Item="Pricing"]}[Data]
        )
    in
        // ...
)

The buffer only helps when the buffered value is defined outside the per-row function and referenced by closure.

Mistake 3: Expecting Buffer to Persist Across Refresh Cycles

Table.Buffer holds data in memory for the duration of a single refresh evaluation. When the refresh completes and you click Refresh again, all buffers are cleared and rebuilt from scratch. If you're seeing unexpected slowness on second refreshes, the buffer is working as designed — it's not a cache that persists between refreshes.

Mistake 4: Buffering Tables That Are Never Referenced More Than Once

This is harmless but wasteful. Buffering consumes memory immediately. If a buffered table is only ever read once (because only one downstream step uses it), you've paid the memory cost without any benefit. Use Query Diagnostics to confirm redundant evaluation before adding buffers.

Mistake 5: Circular Reference Through Buffered Queries

In the layered architecture, if _Staged_A references _Staged_B and _Staged_B references _Staged_A, Power Query will throw a circular dependency error. This is most likely to happen when developers refactor queries and lose track of the dependency direction. Always draw your dependency graph before building the layered architecture. In Power BI Desktop, use View > Query Dependencies to visualize this.

Troubleshooting: Buffer Appears to Have No Effect

If you've added Table.Buffer but performance hasn't improved, the most likely causes are:

1. The bottleneck isn't repeated evaluation — it's a single slow transformation. Check the Diagnostics trace for where time is actually spent.
2. The buffer is defined in a query that isn't being referenced by the slow query — check the query dependency view to confirm the reference chain.
3. Query folding is already consolidating the evaluations — some high-quality connectors (like the native SQL Server connector) implement their own result caching. Run diagnostics with the connector to confirm.
4. The buffered table is very large and RAM pressure is actually making things slower — monitor system memory during refresh.

---

Advanced Pattern: Function Memoization Using Record State

For scenarios where you have a parameterized lookup function — say, looking up exchange rates for specific currency/date combinations — you can implement a form of manual memoization using M's record construction:

let
    Source = Sql.Database("finance-server", "FinanceDB"),
    ExchangeRates = Source{[Schema="dbo", Item="ExchangeRates"]}[Data],
    BufferedRates = Table.Buffer(ExchangeRates),
    
    // Build a two-level lookup: CurrencyCode -> Date -> Rate
    // First, group by CurrencyCode
    GroupedByCurrency = Table.Group(BufferedRates, {"CurrencyCode"}, {
        {"DateRateRecord", each 
            Record.FromList(
                [Rate],
                List.Transform([ExchangeDate], Date.ToText)
            ), 
            type record
        }
    }),
    
    // Build outer record: CurrencyCode -> inner Record
    CurrencyLookup = Record.FromList(
        GroupedByCurrency[DateRateRecord],
        GroupedByCurrency[CurrencyCode]
    ),
    
    // Usage in fact table
    FactTable = Source{[Schema="dbo", Item="FactTransactions"]}[Data],
    
    WithConvertedAmounts = Table.AddColumn(FactTable, "USDAmount", each 
        let
            CurrencyRates = Record.FieldOrDefault(CurrencyLookup, [CurrencyCode], null),
            Rate = if CurrencyRates <> null 
                   then Record.FieldOrDefault(CurrencyRates, Date.ToText([TransactionDate]), null)
                   else null
        in
            if Rate <> null then [LocalAmount] * Rate else null,
        type number
    )
in
    WithConvertedAmounts

This pattern gives you a fully in-memory two-dimensional lookup table with O(1) access time on both keys. The ExchangeRates table is evaluated exactly once and held in BufferedRates and then restructured into CurrencyLookup. Thousands of row-level currency conversions become memory operations rather than database queries.

---

Summary and Next Steps

Table.Buffer is not a magic performance button — it's a precise tool for a specific problem: eliminating redundant evaluations of the same expression in a lazy evaluation engine. Used correctly, it can cut refresh times by 50–80% on query patterns with shared lookups and multi-consumer staging queries. Used incorrectly, it balloons memory usage, destroys folding efficiency, and can make performance worse.

The mental model to carry forward:

1. Identify before you buffer. Use Query Diagnostics to confirm that redundant evaluation is actually happening and where.
2. Buffer after filtering, not before. Let SQL Server, Salesforce, or your source system do the heavy filtering work through folding, then buffer the reduced result.
3. Buffer at the right layer. Shared staging queries that are consumed by multiple downstream queries are the highest-leverage buffering points.
4. Use List and Record patterns for row-level lookups. Record.FieldOrDefault on a buffered, pre-structured lookup record is orders of magnitude faster than Table.SelectRows inside a row function.
5. Document every buffer. Table.Buffer represents an architectural decision about the folding/memory trade-off. Leave a comment.

What to explore next:

• Query Folding Indicators and Native Query Analysis — Learn to read the native SQL generated by Power Query's folding engine and understand exactly where folding breaks. This is the complement to buffering: you want maximum folding before the buffer point.
• M's Value.Metadata and Custom Type Systems — Once you're comfortable with caching strategies, explore how type metadata flows through buffered tables and affects downstream type inference.
• Incremental Refresh and Partition Strategies — Buffering addresses refresh-time performance. Incremental Refresh addresses the fundamental issue of not refreshing data that hasn't changed. These strategies compose well together.
• Power Query in Dataflows Gen2 — The buffering strategies in this lesson apply in Azure Data Factory and Fabric Dataflows as well, but there are important differences in how memory is managed in cloud-hosted evaluation environments.

The investment in understanding M's evaluation model pays compounding returns. Every future query you design will be cleaner, faster, and more maintainable because you know what the engine is actually doing — and you know how to work with it rather than against it.