Table.Buffer and List.Buffer: when buffering helps and when it hurts
Explainer · By Tahir Nas · Examples run in our simulator · Updated
9 min read
Table.Buffer and List.Buffer help when the same table or list is read many times within one query, or when you need a fixed row order that later steps must respect. They hurt when they sit on top of a database or other folding source, because buffering pulls every row into memory and stops later steps from being pushed back to the source.
What buffering actually does
M is lazy and, for lists, tables and binaries, streaming. The language specification's section on immutability says these values are produced on demand each time they are enumerated, so two reads of the same table can in principle return different results if the source or the logic isn't deterministic. A buffer turns that stream into a fixed, in-memory copy.
The Table.Buffer reference adds three details worth knowing:
- Buffering is shallow. Scalar cells (numbers, text, dates) are evaluated and stored; nested values such as a column of tables or records are left as they are.
- It takes an optional record with
BufferMode. BufferMode.Eager buffers the whole value immediately;BufferMode.Delayedworks out the type immediately but waits until the data is needed before buffering all of it. - It "might or might not" speed a query up. Reading every row and storing it costs time and memory, and buffering prevents downstream folding.
List.Buffer is the list equivalent, documented in one line: it returns a stable list held in memory.
What the simulator can and can't show
The examples below run in the site's simulator, which checks the rows they return. It doesn't measure refresh time, so the performance reasoning comes from Microsoft's documentation and from how M evaluates, not from a stopwatch. Time your own refresh before and after adding a buffer; keep it only if it measurably helps.
When buffering helps
A lookup list checked on every row
Filtering one table against a list from another table is a common job: drop orders for discontinued products, keep only customers in a target list. The each function runs once per row, and each run reads DiscontinuedList. Buffering it means the list is built once and reused. A holiday list checked when counting working days between two dates is the same case.
let
Orders = #table(type table [OrderId = number, Product = text, Qty = number], {
{1, "Kite", 2}, {2, "Reel", 1}, {3, "Line", 5}, {4, "Kite", 1}, {5, "Bag", 3}
}),
Discontinued = #table(type table [Product = text], {{"Reel"}, {"Bag"}}),
DiscontinuedList = List.Buffer(Discontinued[Product]),
Active = Table.SelectRows(Orders, each not List.Contains(DiscontinuedList, [Product]))
in
ActiveResult
| OrderId | Product | Qty |
|---|---|---|
| 1 | Kite | 2 |
| 3 | Line | 5 |
| 4 | Kite | 1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Orders 1, 3 and 4 remain. The pattern matters most when the list comes from another query with its own source, because without the buffer that query's column may be re-read as rows are tested. The List.Contains reference covers the function itself, and the exercise to keep orders for a list of products practises the filter.
A small table searched inside a custom column
The same logic applies when every row searches a second table: here, each order looks up the commission rate that applied in its region on its date.
let
Orders = #table(type table [OrderId = number, OrderDate = date, Region = text], {
{1, #date(2026, 2, 10), "North"},
{2, #date(2026, 5, 3), "South"},
{3, #date(2026, 8, 21), "North"}
}),
Rates = Table.Buffer(#table(type table [Region = text, From = date, Rate = number], {
{"North", #date(2026, 1, 1), 0.05},
{"North", #date(2026, 7, 1), 0.06},
{"South", #date(2026, 1, 1), 0.04}
})),
WithRate = Table.AddColumn(Orders, "Rate", (o) =>
List.Last(Table.SelectRows(Rates, each [Region] = o[Region] and [From] <= o[OrderDate])[Rate]),
type number)
in
WithRateResult
| OrderId | OrderDate | Region | Rate |
|---|---|---|---|
| 1 | 2026-02-10 | North | 0.05 |
| 2 | 2026-05-03 | South | 0.04 |
| 3 | 2026-08-21 | North | 0.06 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The rates come back as 0.05, 0.04 and 0.06: order 3 is after the July change, so it picks up the new North rate. List.Last relies on the rate rows being in date order within each region, as they are here; if your rate table isn't, sort it by From before the buffer step. Note where the buffer sits: on the Rates step, outside the per-row function. A Table.Buffer written inside the (o) => … function would run again for every order and give you the cost without the benefit.
Matching each transaction to the latest exchange rate is this kind of search; currency conversion with daily rates shows it in full. Where a lookup is a straight key match rather than a date range, a merge is usually a better tool than a per-row search; the lesson on merge join kinds covers that route.
A list indexed inside a loop
Loops that read items by position, like Amounts{i} in a List.Generate, can enumerate the list again on every step. Buffering the list once before the loop keeps each lookup cheap. The running-total example in List.Generate in Power Query: loops without the confusion uses exactly this pattern.
Making Table.Distinct respect your sort
This is the clearest use Microsoft documents as a correctness fix rather than a speed-up. The Table.Distinct reference says there is no guarantee which duplicate survives, because operations may be folded or skipped, and that you should buffer the table first if you want duplicate removal to behave predictably.
The classic case is "keep each person's most recent signup": sort newest first, then remove duplicates on the email address.
let
Signups = #table(type table [Email = text, SignedUp = date, Plan = text], {
{"ana@example.com", #date(2026, 3, 2), "Free"},
{"ben@example.com", #date(2026, 3, 5), "Free"},
{"ana@example.com", #date(2026, 6, 9), "Pro"},
{"ben@example.com", #date(2026, 4, 1), "Team"},
{"cy@example.com", #date(2026, 5, 20), "Free"}
}),
Newest = Table.Sort(Signups, {{"SignedUp", Order.Descending}}),
Buffered = Table.Buffer(Newest),
Latest = Table.Distinct(Buffered, {"Email"})
in
LatestResult
| SignedUp | Plan | |
|---|---|---|
| ana@example.com | 2026-06-09 | Pro |
| cy@example.com | 2026-05-20 | Free |
| ben@example.com | 2026-04-01 | Team |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Three rows remain: Ana on Pro from 9 June, Cy on Free and Ben on Team from 1 April. Without the buffer step, the result often looks the same in a small preview, which is what makes the bug easy to miss; the documentation is clear that you can't rely on it. If you'd rather not depend on order at all, group by the key and pick the maximum date instead, as the lesson on choosing which duplicate to keep shows. The exercise to keep each person's most recent signup lets you try both.
When buffering hurts
Straight after a database source
Query folding pushes filters, column choices and joins back to a source that can run them, such as SQL Server. Microsoft's overview of query evaluation and query folding explains that this is often faster than pulling all the data and transforming it locally. A buffer ends that: every step after it runs in the Power Query engine on rows already downloaded.
let
Source = Sql.Database("server", "db"),
Sales = Source{[Schema = "dbo", Item = "Sales"]}[Data],
Buffered = Table.Buffer(Sales),
Recent = Table.SelectRows(Buffered, each [OrderDate] >= #date(2026, 1, 1))
in
RecentWithout Buffered, the date filter could become a WHERE clause and only this year's rows would travel. With it, the whole Sales table is read into memory first and filtered afterwards. If a filter step belongs in the query, put it before any buffer. The lesson on query folding and the guide to finding the step that stops folding go further.
When you only wanted to stop folding
Sometimes you do want later steps to run locally, for example when a source translates a step differently from Power Query. For that, the Table.Buffer page itself points to Table.StopFolding, which prevents downstream operations from running against the source without the memory cost of a buffer.
Large tables and values read once
A buffer holds the whole table in memory. On a table of millions of rows that can cost more than it saves, and on a table that the query reads only once there is nothing to save in the first place. Buffering a step that feeds a single filter or a single load is pure overhead.
Nested values you expected to be fixed
Because buffering is shallow, a column that contains tables (after a Group By with All Rows, or a merge before expanding) is not buffered inside. If the expensive part is in those nested tables, buffering the outer table doesn't help.
A quick decision guide
| Situation | Buffer? | Why |
|---|---|---|
| A list or table read on every row of another table | Yes, once, outside the row function | Built once, reused many times |
| A list indexed by position inside a loop | Yes | Each lookup reads a stable copy |
| Sort followed by Table.Distinct to keep a chosen row | Yes, between the two | Microsoft documents it for predictable results |
| First step after a SQL, OData or other folding source | No | Stops filters and joins folding back |
| You only need to stop folding | No, use Table.StopFolding | Same effect on folding, no memory load |
| A large table used once | No | Memory and read time with no reuse |
A useful habit: add a buffer only to fix a specific problem you can name (a repeated read, an order you need kept), check the refresh time, and leave a step name that says why it is there, such as BufferedRatesForLookup. The guide to speeding up slow refreshes puts buffering alongside the other checks worth doing first, and the refresh and performance exercises on Power Query Exercises let you practise them.
Frequently asked questions
Does Table.Buffer make every query faster?
No. Microsoft's own reference says it might or might not, and that it can make queries slower because of the cost of reading and storing all the data and because it blocks downstream folding. Measure before and after.
Should I use Table.Buffer or List.Buffer for a lookup?
Use whichever matches what you read. If the row function only calls List.Contains on one column, buffer that column as a list; it's smaller than the table. If it filters on several columns, buffer the table.
Is there a Binary.Buffer too?
Yes. Binary.Buffer holds a binary value, such as file contents, in memory as a stable copy. The same reasoning applies: it helps when the same binary is read more than once in a query.
Sources
- section on immutability (learn.microsoft.com)
- Table.Buffer reference (learn.microsoft.com)
- BufferMode.Eager (learn.microsoft.com)
- List.Buffer (learn.microsoft.com)
- Table.Distinct reference (learn.microsoft.com)
- query evaluation and query folding (learn.microsoft.com)
- Table.StopFolding (learn.microsoft.com)
- Binary.Buffer (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Incremental refresh in Power BI: what your Power Query must get rightThe Power Query side of incremental refresh: RangeStart and RangeEnd, a half-open date filter, date vs datetime columns, integer date keys and query folding.
- List.Generate in Power Query: loops without the confusionHow List.Generate works in Power Query M: the four functions, record state, the selector, the off-by-one trap and loops for dates, balances and paging.
- Recursive functions in Power Query M with the @ operatorRecursive functions in Power Query M: why the spec needs @ for a function to call itself, a no-@ alternative, a management chain and loop guards.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.