List.Buffer: Speed Power Query Refresh

Key insights

• List.Buffer: The video demonstrates that this Power Query function caches a list in memory so Power Query reads it once instead of re-evaluating it repeatedly.
  It shows how buffering can change query behavior without changing source data.
• Refresh times and real impact: The presenter compares scenarios and shows a dramatic speedup — a refresh that took about 20 seconds dropped to under 1 second after buffering.
  That highlights major gains when the same list is used many times.
• Eager buffering: List.Buffer immediately loads the entire list into memory when called, creating a fixed snapshot for all later steps.
  That avoids repeated source reads but means the list is read up front.
• Best use cases: Use List.Buffer when your M code references the same list multiple times (for example inside loops or row-by-row "each" functions) or when repeated evaluations slow refresh, especially on large transforms.
• Memory trade-off: Buffering speeds up queries but increases memory use because the full list is stored at once; test on large lists to avoid running out of memory or causing slower performance.
• Implementation tips: Insert List.Buffer as an explicit step before expensive transforms, then run a refresh time test and compare results.
  Keep the buffered list isolated and measure performance to confirm the benefit.

Video overview: why this matters

The YouTube video from Excel Off The Grid focuses on a single Power Query function that can dramatically shorten refresh times. The presenter walks through a clear scenario and shows side-by-side tests that highlight the effect of adding a List.Buffer step. Consequently, the demonstration makes a strong case for considering buffering when queries reference the same list repeatedly.

Moreover, the video timestamps give a concise roadmap of the demonstration, including short and large data scenarios, the addition of List.Buffer, and speed tests. This structure helps viewers reproduce the tests and observe the differences themselves. As a result, the video is practical for both novices and experienced users who troubleshoot slow refreshes.

What List.Buffer does and the demo findings

At its core, List.Buffer captures a snapshot of a list and keeps it in memory so Power Query does not re-evaluate the source each time the list is referenced. In the video, a simple test reduces a refresh from about 20 seconds to roughly 0.8 seconds after buffering, demonstrating a dramatic improvement. The presenter repeats the experiment with both a small (100 rows) and a large (100,000 rows) dataset to show that the benefit scales in scenarios where repeated list access occurs.

Importantly, the performance gain comes when the M code repeatedly accesses the same list inside row-by-row logic or iterative functions. Therefore, buffering prevents expensive re-computation or additional calls to the original data source. The measured speed improvements in the video are compelling, but they also depend on how the query is written and where the buffer sits in the transformation chain.

How buffering works: eager caching and consequences

List.Buffer operates by eagerly populating memory with the list content at the moment the function runs, rather than waiting until parts of the list are needed. That immediate evaluation guarantees a consistent cached version, which stops the repeated execution of the underlying query or expensive computations. However, the eager approach also means Power Query reads and stores the entire list up front, which carries implications for memory usage.

Consequently, buffering offers a tradeoff between CPU and I/O savings on repeated operations versus higher memory footprint at the point of buffering. For moderate lists the trade is often worthwhile, but very large lists can risk excessive memory consumption or even cause the query to fail. Thus, understanding the data size and the execution context is essential before applying a blanket buffering strategy.

Tradeoffs and practical challenges

While buffering can reduce refresh time significantly, the technique brings tradeoffs that the video highlights indirectly and that users must weigh. For instance, buffering can defeat query folding to the source, preventing remote servers from performing filters or aggregations and thereby increasing local processing. Additionally, buffering can create a higher peak memory requirement, which may be problematic on constrained machines or when multiple queries buffer simultaneously.

Another challenge lies in diagnosing when buffering helps: not all slow refreshes come from repeated list access. Some performance problems stem from inefficient joins, unnecessary row-level functions, or lack of indexing in the source. Consequently, users should profile queries, test with representative data sizes, and confirm that repeated list evaluation is the root cause before inserting a List.Buffer step.

Best practices and recommendations

First, place buffering just before the point where the list is referenced repeatedly rather than at the earliest possible step, because that reduces the amount of data you hold in memory. Second, test performance on scaled-up datasets to detect memory pressure or unexpected slowdowns, and compare buffered versus non-buffered runs. Third, consider alternatives such as refactoring the logic to avoid repeated evaluations, using table buffering when more appropriate, or leveraging server-side processing to preserve folding.

Finally, adopt an iterative approach: measure baseline times, apply List.Buffer, and then reassess both refresh time and memory usage. By weighing these factors and monitoring results, teams can achieve a balance between speed and resource consumption. Ultimately, the video presents List.Buffer as a powerful tool, but not a one-size-fits-all solution, and it encourages informed testing before wide deployment.

Conclusion: when to apply buffering

The Excel Off The Grid video provides a focused, practical demonstration that makes List.Buffer an important technique in the Power Query toolbox. Yet, buffering requires careful placement and testing because of its eager memory allocation and potential impact on query folding. Therefore, apply buffering selectively and verify gains with real-world data and environment constraints.

Overall, the video serves as a useful tutorial for identifying and addressing a common performance problem, while also reminding users to weigh speed improvements against memory and processing tradeoffs. By following the recommended checks and experiments, analysts can often unlock large refresh-time savings without compromising stability or resource limits.

Keywords

Power Query List.Buffer, List.Buffer performance, speed up Power Query refresh, Power BI refresh optimization, improve query performance Power Query, M language List.Buffer tips, reduce Power BI refresh time, Power Query performance best practices