Power Query: Fix Slow SharePoint Refresh

Key insights

• SharePoint.Files vs SharePoint.Contents: SharePoint.Files scans every file across the site and can make refreshes very slow.
  Switching to SharePoint.Contents limits the query to the specific folder you need and often cuts refresh time dramatically.
• Query folding: Query folding pushes filters and column removal to the data source so Power Query does less work locally.
  Apply filters and remove unused columns early to keep folding where possible, but expect limitations with many SharePoint setups.
• Multi-query architecture: Build a main "processed" query with all transforms and then create a simple reference query for reporting.
  This prevents repeated full processing during development and reduces unnecessary refresh cycles.
• Merges and alternatives: Merging many tables or files increases refresh cost.
  Use star/snowflake layouts, pre-filtered sources, or functions like List.Buffer and List.Contains where appropriate to avoid heavy merges.
• Development sampling: Work on a small subset of files or the top N rows while you develop transformations.
  Limiting data during development speeds iteration and avoids full-site scans until you finish tests.
• Parallel loading and incremental refresh: Enable parallel table loading to reduce total refresh time, but test it because it can sometimes slow queries that depend on each other.
  For time-series tables, use incremental refresh to update only recent data rather than reloading everything.

Excel Off The Grid’s recent YouTube video explains a practical fix for painfully slow refreshes when using SharePoint data in Power Query, and it demonstrates performance improvements that can reach up to 700% faster. The video compares two connectors—SharePoint.Files and SharePoint.Contents—and measures the difference in refresh times for a realistic scenario. As a result, viewers can see both the technical reason behind the slowdown and a step-by-step conversion process that reduces unnecessary scanning across an entire SharePoint site. Consequently, the piece offers usable guidance for analysts and BI developers who rely on SharePoint as a data source.

Why SharePoint refreshes can be slow

The video begins by showing that SharePoint.Files can be very slow because it scans every file on a SharePoint site before Power Query applies transformations. In practice, this behavior forces Power Query to download or enumerate many files, which multiplies latency for sites with deep folder trees or many documents. Moreover, SharePoint connectors often do not support full Query Folding, so you cannot push filters to the server to avoid transferring large volumes of data. Therefore, the bottleneck is both enumeration overhead and the inability to offload work to the source.

Because of these limitations, refresh times that should normally take seconds can stretch into minutes or even hours for larger datasets. The author measures average refresh times for both connectors to quantify the impact and help viewers decide whether a conversion is worthwhile. Thus, understanding the scanning behavior is the first step toward choosing the right connector or redesigning queries. Overall, the video frames the problem with clear examples so readers can relate it to their own SharePoint workloads.

How SharePoint.Contents changes the equation

Next, the presenter demonstrates that using SharePoint.Contents limits the query to a specific folder structure rather than scanning the entire site, which dramatically reduces refresh time. By narrowing the scope to the folder you actually need, Power Query processes far fewer items and spends less time enumerating metadata. The video runs side-by-side comparisons and reports average refresh times to show the real-world benefit, making the improvement immediately verifiable. Consequently, switching connectors can appear to be the quickest route to faster refreshes in many scenarios.

However, the video also highlights that SharePoint.Contents is not a drop-in replacement for every use case because it returns content differently and may omit some file-level metadata that SharePoint.Files exposes. Therefore, the tradeoff is between completeness of metadata and refresh speed: you gain performance but might need extra steps to reconstruct lost fields. For that reason, the presenter recommends testing the results and validating that all required columns survive the conversion. In short, faster is not always better if it breaks downstream logic.

Conversion steps and practical advice

The tutorial includes a clear walk-through for converting a query from SharePoint.Files to SharePoint.Contents, starting with navigating to the folder and applying filters that limit the returned items. Then the author shows how to filter by folder path and combine binaries when necessary, preserving the transformation logic while restricting input volume. During the conversion, viewers learn to reapply column removals and filters early so that subsequent steps work on smaller datasets. As a result, iteration becomes noticeably faster during development and deployment.

Additionally, the presenter recommends development strategies such as sampling files and limiting rows while building transformations, which speeds iteration without sacrificing correctness. He also suggests using functions like List.Buffer and list-based filters in place of expensive merges when appropriate, and creating a multi-query architecture where one processed query feeds referenced queries. These methods reduce redundant processing and help maintain responsiveness as queries evolve. Ultimately, the video balances practical conversion steps with broader design patterns.

Tradeoffs, parallel loading, and incremental patterns

Finally, the video explores broader tradeoffs and advanced techniques, noting that enabling parallel loading can improve refresh speed for independent tables but can harm performance when queries share the same source. Therefore, toggling parallel loading requires testing in your environment rather than assuming a universal benefit. The author also explains that incremental refresh is a powerful pattern for time-series data because it avoids reprocessing unchanged historical rows, but it adds complexity and governance requirements. Thus, teams must weigh the reduced refresh footprint against the effort to configure and validate incremental policies.

In conclusion, Excel Off The Grid’s video provides a balanced, evidence-based approach to speeding up SharePoint-backed Power Query refreshes by switching connectors and applying architecture changes. While the potential gains are substantial, the video emphasizes careful testing, validation of metadata, and thoughtful query design to avoid unintended side effects. Consequently, analytics teams should consider both immediate speed wins and the longer-term maintainability of their queries before adopting a single solution. Ultimately, the tutorial equips practitioners with actionable steps and the context needed to choose the right tradeoffs for their projects.

Keywords

power query slow refresh, speed up power query refresh, sharepoint power query performance, optimize power query refresh, power bi sharepoint refresh, improve power query refresh time, incremental refresh power query, fast power query refresh from sharepoint