List.Buffer
ListBuffers a list in memory, forcing immediate evaluation and preventing repeated re-evaluation of the source.
Syntax
List.Buffer(list as list) as listParameters
| Name | Type | Required | Description |
|---|---|---|---|
list | list | Yes | The list to buffer into memory. |
Return Value
list — An in-memory copy of the list.
Remarks
List.Buffer forces the immediate and complete evaluation of a list and stores the result in memory. The returned list is identical in content to the input but is backed by an in-memory snapshot rather than a lazy query pipeline. Subsequent references to the buffered list read from memory without re-executing the source query.
This is a performance optimization function. In Power Query, lists are lazy — they are evaluated on demand, potentially multiple times. The most critical use case for List.Buffer is when a list derived from an external source (such as a table column) is used inside Table.SelectRows as the membership list for a List.Contains check. Without buffering, Power Query re-evaluates the entire source list on every row of the table being filtered, which causes O(n²) performance on large datasets.
List.Buffer is the list equivalent of Table.Buffer. Use it whenever a list will be referenced multiple times or when the source of the list is expensive to compute (a database query, web call, or large file read).
Examples
Example 1: Buffer a lookup list used inside Table.SelectRows
let
Sales = #table(
{"OrderID", "CustomerName", "Product"},
{{1,"Alice","Widget A"},{2,"Bob","Gadget B"},{3,"Charlie","Widget C"},{4,"Alice","Gadget D"},{5,"Diana","Widget A"}}
),
AllowedProducts = List.Buffer({"Widget A", "Widget C"}),
Filtered = Table.SelectRows(Sales, each List.Contains(AllowedProducts, [Product]))
in
FilteredOrderID | CustomerName | Product | |
|---|---|---|---|
| 1 | 1 | Alice | Widget A |
| 2 | 3 | Charlie | Widget C |
| 3 | 5 | Diana | Widget A |
Example 2: Buffer a list used in multiple aggregations
let
Values = List.Buffer(List.Numbers(1, 100)),
Count = List.Count(Values),
Total = List.Sum(Values)
in
#table({"Count", "Total"}, {{Count, Total}})Count | Total | |
|---|---|---|
| 1 | 100 | 5,050 |
Example 3: Buffer random numbers to prevent re-evaluation
let
Randoms = List.Buffer(List.Random(5, 42)),
Min = List.Min(Randoms),
Max = List.Max(Randoms)
in
#table({"Min", "Max"}, {{Min, Max}})Min | Max | |
|---|---|---|
| 1 | 0.22 | 0.88 |