Power Query: References vs Connections

Key insights

• Power Query test on YouTube compares loading the same workbook 10 times versus loading it once and using reference queries, and the results surprised the presenter.
  
• Microsoft guidance does not state that references always run faster; queries can run independently and may hit the source multiple times during a refresh.
  
• Reference starts from another query’s result while Duplicate copies the full query so it can change independently; use references for reuse and duplicates for separate experiments.
  
• Performance depends more on Query folding and buffering than on whether you use references or multiple direct connections; references do not guarantee fewer source reads.
  
• To improve refresh speed, filter early, remove unused columns, preserve folding where possible, and capture refresh traces to see if the source is queried repeatedly.
  
• Bottom line: use references for maintainability, but always test refresh behavior on large or costly sources and consider staging or buffering to avoid repeated reads.
  

Video at a glance

Excel Off The Grid published a concise test video that asks a practical question for many spreadsheet users: in Power Query, is it faster to load the same workbook multiple times or to load it once and create Reference queries? The video sets up a controlled experiment and walks viewers through timing results, buffering behavior, and variations in connection patterns. Consequently, the piece serves both as an instructional demo and as a prompt to revisit common assumptions about query best practices.

Importantly, the author frames the test against prevailing community and vendor guidance, and then runs hands-on comparisons. The video is structured with clear stages and timestamps so viewers can fast-forward to specific tests. As a result, the demonstration becomes easy to replicate and validate for curious analysts.

Experiment setup and method

In the recorded test, the creator compares three main scenarios: ten direct connections to the same source, a single connection with multiple Reference queries, and ten unique connections that differ slightly in query logic. Each scenario uses the same workbook source and measures the elapsed time for refresh operations, while also testing whether buffering or caching changes the outcome. Thus, the method emphasizes reproducibility and side-by-side timing to reduce ambiguity.

The video also explores how intermediary steps such as buffering affect runtime. For that reason, the author includes an explicit buffered version of a query to show how forcing an in-memory snapshot can change behavior. Therefore, the setup highlights not just raw timings but how architectural choices inside Power Query influence performance.

Surprising results

Contrary to the common rule of thumb that you should always read a source once and then reference that query, the test produces unexpected timings. Although conventional wisdom favors Reference queries for performance, the video shows cases where multiple direct connections perform similarly or even better under specific conditions. Consequently, the evidence undermines the blanket assumption that referencing always reduces data source hits or runtime.

Moreover, the author demonstrates that caching and query folding play a larger role than the mere presence of references. When queries preserve folding or when buffering is introduced, the refresh behavior can change markedly and collapse multiple operations into fewer source reads. Thus, the performance picture becomes more conditional than many practitioners assume.

Tradeoffs and technical challenges

There are clear tradeoffs between maintainability and raw execution behavior. On one hand, Reference queries simplify maintenance by reducing duplicated logic and making transformations easier to update; on the other hand, they do not guarantee fewer source hits during refresh. As a result, teams must weigh improved readability and easier edits against the uncertain runtime implications when scaling refreshes or connecting to expensive external sources.

Additionally, the video underlines several technical challenges. For instance, whether a query folds to the source, whether intermediate buffering is applied, and how the engine parallelizes refreshes all influence performance in nonobvious ways. Therefore, diagnosing slow refreshes requires tracing the actual queries sent to the source rather than relying on naming or structure alone, which complicates troubleshooting in production environments.

Practical guidance and takeaways

The practical guidance offered in the video reconciles design and performance goals. First, use Reference queries for reuse and maintainability because they reduce duplicated logic and make updates easier, and second, treat them cautiously when performance is critical because they do not automatically prevent repeated source evaluations. Consequently, the recommendation is to test real refresh traces for your own workload instead of adopting a single rule.

Finally, the author suggests common-sense optimizations that matter more than the reference-versus-connection choice: filter early, remove unused columns, preserve query folding where possible, and consider buffering when you must avoid repeated expensive reads. In short, the video by Excel Off The Grid encourages practitioners to combine clean design with measurement-driven tuning, since the best approach depends on source behavior, query folding, and the scale of data operations.

Keywords

Power Query performance test, Power Query references vs multiple connections, Power Query optimization tips, Power Query multiple connections performance, Power Query references performance, Power Query speed comparison, Power Query best practices, Power Query query folding impact