Table.Buffer
Loads the table into memory once so that later steps reuse it, and fixes the row order from that point.
Table.Buffer(table as table) as tableReturns: 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
- table: the table to hold in memory. Required.
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
- Use it on small and medium tables. A large table held in memory can slow or fail a refresh.
- It breaks query folding from the buffered step onward. Filter and reduce the data on the database before you buffer it.
- It is not needed for most queries. Add it only when a step depends on an order you have set, or when a table is read many times.
- A buffered table is held per refresh, so the copy is not shared between queries.
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.