Downloads
Power Query MList: transform and filterAdvanced

List.Buffer

Loads a list into memory once, so later steps reuse the same values instead of recalculating them.

Syntax
List.Buffer(list)

Returns: The same list, held in memory.

What it does

Power Query normally re-evaluates a step each time something refers to it. List.Buffer tells it to evaluate the list once and keep the result in memory. Use it when the same list feeds several calculations, or when a list is expensive to make, such as one built with List.Accumulate or read from a slow file.

Syntax and arguments

Example

A close checklist uses the same list of approved vendor codes in three different checks. Buffering the list means it is read once.

let
    ApprovedVendorCodes = {"V-1001", "V-1002", "V-2044", "V-1001"},
    Codes = List.Buffer(List.Distinct(ApprovedVendorCodes)),
    CheckA = List.Count(Codes),
    CheckB = List.Select(Codes, each Text.StartsWith(_, "V")),
    CheckC = List.Contains(Codes, "V-1001")
in
    CheckC

Each check uses the same stored list. CheckC is true if the code exists in the approved list. Without the buffer, the distinct step could run three times.

Example: a second, different use

Buffering a sorted list that a running total depends on, so the sort does not repeat for every row.

Things to watch

In business terms

Reference lists such as approved vendors, valid account codes or the chart of accounts are read many times in a close. Buffering keeps the workbook responsive and makes the query behave the same way on every check.

Related functions

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