Downloads
Power Query MTable: headers and shapeAdvanced

Table.Buffer

Loads the table into memory once so that later steps reuse it, and fixes the row order from that point.

Syntax
Table.Buffer(table as table) as table

Returns: The same table, held in memory.

What it does

Table.Buffer tells Power Query to read a table once and keep a copy in memory. Two things follow. Later steps that use the table do not re-run the source each time, and the row order is fixed, so an operation that depends on order, such as keeping the first row in a sorted list, behaves predictably. The cost is memory, and buffering also stops query folding from that point on.

Syntax and arguments

Example

A payments list is sorted with the latest payment first, and then one row is kept for each customer. The buffer ensures the row kept for each customer is the latest one in the sorted order.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Payments = Source{[Schema = "classicmodels", Item = "payments"]}[Data],
    Sorted = Table.Buffer(Table.Sort(Payments, {{"paymentDate", Order.Descending}})),
    Latest = Table.Distinct(Sorted, {"customerNumber"})
in
    Latest

Without the buffer, Table.Distinct can keep a different row than the one the sort placed first. The buffer makes the kept row the one you intended.

Example: a second, different use

A small list of approved account codes is read once and used by several later steps, so it is not reloaded for each use.

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

Things to watch

In business terms

Control lists, such as approved vendors or the chart of accounts, are small and used many times. Buffering them is a simple way to keep the work fast and the order of a sorted list reliable.

Related functions

All Power Query M functions · View as Markdown · M basics