Table.Buffer

Table.Buffer is a Power Query M function that buffers a table in memory, isolating it from external changes during evaluation. The function returns a buffered table with scalar cell values evaluated, but non-scalar values remain unchanged.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.Buffer function buffers a table in memory, isolating it from external changes during evaluation. Buffering is shallow. It forces the evaluation of any scalar cell values, but leaves non-scalar values (records, lists, tables, and so on) as-is.

You can specify a BufferMode.Type. The function supports:

• BufferMode.Eager: This option loads all the data into memory right away.
• BufferMode.Delayed: This option waits to load the data until it’s needed. At that point the entire table is immediately buffered

Using Table.Buffer can either speed up or slow down your queries. Reading all the data and storing it in memory may add time to your query. Also, using this function can prevent certain optimizations (known as “downstream folding”). If you only want to prevent these optimizations, use Table.StopFolding instead.

Examples

Load all the rows of a SQL table into memory, so that any downstream operations will no longer be able to query the SQL server.

// Output: table
let
    Source = Sql.Database( "SomeSQLServer", "MyDb" ),
    MyTable = Source{[Item="MyTable"]}[Data],
    BufferMyTable = Table.Buffer( dbo_MyTable )
in
    BufferMyTable

You can specify the eager buffer mode with the following code:

    BufferMyTable = Table.Buffer( dbo_MyTable, [ BufferMode = BufferMode.Eager ] )

Why use BufferMode.Delayed?

But what is BufferMode.Delayed good for? BufferMode.Delayed is useful to make development faster.

Imagine you’re working with a large SQL database and you only need a subset of columns from a particular table. When you load your query Power BI normally has a moment where it ‘evaluates the query’.

The time where it evaluates the query can be significantly reduced by using the BufferMode.Delayed.

let
    Source = Sql.Database( "SomeSQLServer", "LargeDb" ),
    MyTable = Source{ [Item="HugeTable"] }[Data],
    SelectedColumns = Table.SelectColumns( MyTable, {"Column1", "Column2", "Column3"} ),
    BufferSelectedColumns = Table.Buffer( SelectedColumns, [ BufferMode = BufferMode.Delayed ] )
in
    BufferSelectedColumns

Reducing the evaluation time is great during development, although the actual loading time of the data will be identical. Validation time in dataflows especially, can be much faster too.

Related articles

Learn more about Table.Buffer in the following articles:

• Removing Duplicates in Power Query (Complete Guide)
  Removing duplicates in Power Query – This guide explores basic and advanced techniques and why you may be getting inconsistent results. » Read more
• New Options For The Table.Buffer Function In Power Query
  The article discusses BufferMode options for the Table.Buffer function to speed up the query evaluation process, especially beneficial during development and to speed up dataflow validation. » Read more

Related functions

Other functions related to Table.Buffer are:

• Binary.Buffer
• List.Buffer

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-buffer