Table.Buffer Function in Power Query M (4 Easy Examples)

If you want to stop Power Query from re-reading the same table over and over, or keep a table’s row order stable before you add an index, Table.Buffer is the function for this.

It loads a table fully into memory so it gets evaluated once. In this article, I’ll show you how this Power Query function works and when it actually helps.

Table of Contents

Syntax of Table.Buffer Function

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

where

table (required, table). The table you want to load into memory and evaluate a single time.
options (optional, nullable record). Extra settings, mainly BufferMode. It accepts BufferMode.Eager (the default, read everything now) or BufferMode.Delayed (read on first use).

Returns: the same table, now held in memory so it is evaluated once. The data you see is unchanged. The benefit is behavioral, not a transformation.

In plain terms, you hand it a table and you get back the exact same table, just pinned in memory so Power Query stops fetching it again.

Example 1: Buffer a table and get the same data back

The first thing to understand is that buffering does not change your data. The buffered result equals the source, row for row.

Here is the starting data:

Region	Units
North	40
South	25
East	18
West	33

Wrap the source in Table.Buffer:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Buffered = Table.Buffer(Source)
in
    Buffered

The output is the same table, now buffered in memory.

The result is the same table:

Region	Units
North	40
South	25
East	18
West	33

Nothing visible changed. What changed is that Source is now read once and held, instead of being re-fetched each time something downstream needs it.

Example 2: Freeze the order before adding an index

Table.Buffer is handy when you sort a table and then add an index. Buffering after the sort locks the order in, so the index numbers line up with the rows you sorted.

Here is the starting data:

Player	Points
Mia	70
Leo	92
Zoe	85
Sam	61

Sort by Points descending, buffer, then add a Rank column:

let
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
    Sorted = Table.Sort(Source,{{"Points",Order.Descending}}),
    Buffered = Table.Buffer(Sorted),
    Ranked = Table.AddIndexColumn(Buffered,"Rank",1,1,Int64.Type)
in
    Ranked

The order is now fixed, so each rank maps to the right player.

The result:

Player	Points	Rank
Leo	92	1
Zoe	85	2
Mia	70	3
Sam	61	4

The buffer step guarantees the sorted order is final before the index is applied, so the ranking stays correct.

Example 3: Buffer once, reference twice

When a table is used more than once downstream, buffering it means the source is read a single time. Here the same buffered table feeds both a row count and the column that displays it.

Here is the starting data:

Code	Label
A	Alpha
B	Bravo
C	Charlie

Buffer the source, count its rows, then add that count as a column:

let
    Source = Excel.CurrentWorkbook(){[Name="Lookup"]}[Content],
    Cached = Table.Buffer(Source),
    RowCount = Table.RowCount(Cached),
    WithCount = Table.AddColumn(Cached,"TotalCodes",each RowCount,Int64.Type)
in
    WithCount

Cached is referenced by both RowCount and WithCount, but the source is only read once.

The result:

Code	Label	TotalCodes
A	Alpha	3
B	Bravo	3
C	Charlie	3

Without the buffer, a volatile or slow source could be evaluated twice here. With it, the data is fetched once and reused.

Example 4: Buffering forces local evaluation

This is the caveat to watch for. Table.Buffer pulls everything into memory, which means any step after it runs locally instead of being pushed back to the source.

Here is the starting data:

Client	Amount
Acme	1200
Globex	450
Initech	900
Umbrella	300

Buffer the source, then filter for amounts of 500 or more:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    Buffered = Table.Buffer(Source),
    Filtered = Table.SelectRows(Buffered,each [Amount]>=500)
in
    Filtered

The filter still works and returns the right rows.

The result:

Client	Amount
Acme	1200
Initech	900

The output is correct, but on a foldable source like a database, the buffer would stop that filter from being sent to the server. It would run in memory instead, which can be slower. That tradeoff is the whole reason to be careful with where you buffer.

When should you use Table.Buffer

Use it when a table is referenced multiple times downstream, so the source is read once instead of repeatedly.
Use it to freeze row order right before adding an index column.
Use it to stop a slow or volatile source (a web call, a random function, a remote query) from running again.
Skip it when the source is small or already cheap to read. The memory cost is not worth it.
Skip it on a foldable database source you want to keep folding, because buffering breaks that.

Things to keep in mind with Table.Buffer

It breaks query folding. Buffering forces everything into memory, so steps after it run locally and stop being pushed to the source. On a foldable database source this can actually slow things down (see Example 4). If you only want to stop folding without holding the data, use Table.StopFolding instead.
Buffering is shallow. It forces the scalar cells of the table, but any nested values (tables or lists inside cells) stay lazy and are not buffered.
It costs memory. Table.Buffer reads the entire table in at once, so it’s a tradeoff, not a free speedup. On a large table that memory hit can outweigh the gain.
There are siblings for other types. Use List.Buffer to pin a list in memory and Binary.Buffer for binary values.

Common questions about Table.Buffer

What is the difference between Table.Buffer and List.Buffer?

They do the same job for different shapes of data. Table.Buffer pins a whole table in memory, while List.Buffer pins a list. Reach for List.Buffer when the value you keep re-evaluating is a list, such as a lookup of valid codes used inside a row filter.

Does Table.Buffer make every query faster?

No, and that’s the key thing. It’s a tradeoff. It helps when it stops a slow source from being read again, but it costs memory and breaks query folding. On a small source, or a database where folding was doing the work, buffering can make a query slower instead.

List of All Power Query Functions

Related Power Query Functions / Articles: