Fix Slow Refreshes: Faster Way to Load SharePoint Files in Power BI

Vojtěch Šíma
Dec 30, 2025
8 min read

tl;dr If you want the best performance for a large number of files, use SharePoint.Contents with ApiVersion = 15 and place all the files you need inside a single folder. To understand why and learn more details, read the full blog post.

Difference between SharePoint.Files and SharePoint.Contents

SharePoint.Files and SharePoint.Contents are two built-in functions in Power Query used to retrieve SharePoint data easily with a user-friendly clicking experience. Well, at least the first one is. If you click your way through and select "SharePoint folder" from a list of connectors, the SharePoint.Files function will be applied automatically.

You perhaps heard about the problems or potential slowness of using the automatic function, so you manually retyped "Files" to "Contents" and now you are using SharePoint.Contents.

Are your problems solved now?

The easy explanation for why one or the other is better/faster/stronger is as follows. In the realm of clicking, when using Files, you list all of the binary content from the SharePoint site and then you pick what you want by filtering or perhaps directly selecting. In Contents, you have simple navigation that traverses over folders; similar to any folder manager, you're basically doing just that.

Why is it slow, and who is faster?

Generally, Contents would be faster, BUT it depends on a couple of factors. In non-technical speak (I'll explain in great technical detail later, don't worry), the speed will be tied to the overall number of files on your SharePoint and the exact version of Contents/Files you use. At this moment, if you don't provide any version, Contents will run at 14. This version behaves similarly to the default Files behaviour: retrieve metadata and discovery, then (in the Contents case) navigate folder by folder or according to the Navigation you defined, retrieve context information about the file, and finally, retrieve the file's contents.

This is kinda inefficient, and Contents with the default API version can perform even worse than the same test with Files.

With ApiVersion 15, Contents suddenly makes sense. It skips most metadata checks and discoveries, and retrieves content by folders immediately, mirroring your navigation. This is way faster.

Now, is it faster than Files? Well, here is the "depends" part: the more files you have on your site, the faster Contents will be, but not drastically. Even though Files sends extra web requests, the speed is not that much worse, given that you try to mimic the same behaviour as with Contents. The difference should be tied to the Files discovery phase, which is paged by the thousand, so once you hit 1001 files, you should start pulling ahead a tiny bit. You also technically do an extra request for every single file, so these milliseconds will add up.

To summarize, using ApiVersion 15 and SharePoint.Contents should be faster.

= SharePoint.Contents("https://tenant.sharepoint.com/sites/siteName", [ApiVersion=15])

There is also an option to choose Implementation, but when using ApiVersion=15 and Implementation="2.0" versus not using that implementation, I didn't find much difference.

Benchmark Results

Common Test Setup

Files Loaded: 79 (Identical .xlsx files)
File Size: 1.25 MB per file
Data Structure: 4 Columns, 58,605 Rows per file
Total Volume: 4,629,716 Rows
Accuracy is approximately ±5 seconds.

Scenario #1

Context: 7,325 total files on the site.

Connector	Function Options	Time (mm:ss)
SharePoint.Contents	ApiVersion = 14 (Default)	02:00
SharePoint.Files	ApiVersion = 15 (Default)	01:00
SharePoint.Contents	ApiVersion = 15	00:53
SharePoint.Contents	ApiVersion = 15, Implementation = 2.0	00:52

Scenario #2

Context: 26,573 total files on the site.

The setup is identical to Scenario #1, but with significantly more files located outside the scoped folder, yet within the same site.

Connector	Function Options	Time (mm:ss)
SharePoint.Contents	ApiVersion = 14 (Default)	05:03
SharePoint.Files	ApiVersion = 15 (Default)	01:30
SharePoint.Contents	ApiVersion = 15	00:50
SharePoint.Contents	ApiVersion = 15, Implementation = 2.0	00:55

The long load time when using ApiVersion = 14 of SharePoint.Contents is primarily due to old OData requests performing discoveries; these requests lowkey prohibit themselves from being sent faster.

From the benchmarks, you can see that with an increase in files, the SharePoint.Files connector needs to perform additional discoveries, which slows it down. In contrast, SharePoint.Contents (using ApiVersion = 15) is stable, and the numbers are identical (within the noted accuracy). Therefore, with the right usage, SharePoint.Contents should be the winner.

More details on corelation with amount of files and the speed is below

Additional Note on Combining Files

Also, one extra note: whether you use the easy clicking file combine option (the ugly built-in thing that creates a bunch of extra fluff/stuff) or you define a transformation for each file and apply it yourself, nothing really changes regarding speed or processing. But obviously, building it yourself is cleaner and easier to manage, naturally... and I give you extra points for that.

An alternative fast way to load SharePoint files

Now, let's see if there is an even faster way. Spoilers: yeah, but only a tiny bit. For real, tho, you will perhaps get one or two seconds down.

SharePoint itself isn't really meant to be used for bulk file retrieval, and it's not really the right storage either. Microsoft definitely offers better solutions, but for this article, I will ignore those. I could simply say the fastest way to load SharePoint files is to move them elsewhere, parse them into a database, and get the data from there, but that's kind of against the purpose of this whole article.

If there is some reader who likes to say "ehm, ackchyually," sure, you're right: either implement incremental refresh on the SharePoint source or move the files away. There is definitely a better way than daily ingesting tens or hundreds of files into Power BI.

With that out of the way, let's look at how to get the files directly from SharePoint fast with an alternative method, inside Power Query.

SharePoint.Files vs SharePoint.Contents from a technical perspective

Earlier, I promised you technical details about the internal workings of SharePoint.Files and SharePoint.Contents. To understand and better grasp the fastest way to gather files, allow me to first introduce the key concepts we need to build a better solution.

For this, I'll use ApiVersion = 15 for both, so the tests are fair.

SharePoint.Files

The process starts with a call to gather the Document library. This is done by sites/Experiments/_api/web/lists/? with filters to retrieve non-systemic documents with more than 'zero' files.

Then it sends requests for metadata, and with the document library ID, it starts the discovery phase through /RenderListDataAsStream as a POST request. This is an expensive way to retrieve items on SharePoint. It is expensive because it returns a bunch of extra data; this is kind of meant for visual representation on the web, so for background jobs, this is way too heavy.

And it scans all folders and subfolders for files only. This scan is extremely weird; by default, it scans the whole site by 1,000 items per page. However, if the folder and files you want are early in the list, it may stop the scanning once it finds them. But the "may" is the weird part.

Here is an example: Let's say your existing folder has ID 7489. All files underneath are direct and subsequent, perhaps from ID 7490 to 7800. All of them and any subfolder in that path fit inside that specific page, so then it 'may' stop scanning. However, if you now introduce a new file to that folder with ID 13000 because in the meantime you created 5,000 files elsewhere, you can break the sweet scanning. The engine now has to page further through the site inventory to find that last file. That is the theory I have gathered so far.

I am still not fully satisfied with this behaviour, so I will keep digging into it. But basically for now, just remember: each file and folder has a unique ID which is created sequentially. If you add new files to older folders, it will have to scan the whole site, as it patches chronologically, hence increasing the time to load data.

Once you find the data, for each file, it sends api/contextinfo. This is required because of the digest render and contains metadata, but it is just half a kilobyte big, so not a big deal. Finally, it downloads the content of a file with api/web/getfilebyserverrelativeurl(path)/$value, which is the actual file.

SharePoint.Contents

This is way more straightforward and without headaches. The process starts by replicating the navigation you configured in your Power Query steps. So, it is going to send a bunch of api/Web/GetFolderByServerRelativePath(path)/Folders and api/Web/GetFolderByServerRelativePath(path)/Files requests from the root folder of the library to the last folder you defined in your steps.

Once this is done, similarly to SharePoint.Files, it will start getting the actual files with _api/web/getfilebyserverrelativeurl(path)/$value, and that is kind of it.

Custom solution to retrieve data from SharePoint

Now that we understand what SharePoint does under the hood, we can replicate that process but optimize it. I explored other potential endpoints to see if we could retrieve data in bulk more efficiently, but for Power Query use cases, the options are limited. Even the Microsoft Graph API does not offer a significant speed advantage for this specific sequential file retrieval, and brings extra security setup.

There is technically a /batch endpoint that allows you to bundle requests. However, parsing the multipart responses for complex binaries like Excel files is kinda difficult in M and does not yield enough performance gain to justify the complexity. I also wanted to prioritize ease of use regarding authorization. The solution below relies on the built-in organizational account authentication you already use for SharePoint.Contents, saving you from building a custom OAuth2 flow.

The strategy is straightforward. We will assume all your target data resides inside a specific folder and does not require complex filtering. In case you need that filtering, you can simply expand more fields and do any modifications after loading the file's metadata. We will simply list all files inside that target folder to get their ServerRelativeUrl. Then, similar to the native connectors, we will retrieve the content one by one using GetFileByServerRelativeUrl. This approach minimizes the number of requests because we bypass the iterative folder-by-folder navigation and jump straight to the source.

let
    // config here
    tenantUrl = "https://tenant.sharepoint.com",
    siteUrlPath = "sites/SiteName",
    targetFolderServerRelativeUrl = "/sites/SiteName/Shared Documents/TargetFolder",

    getListOfFiles = 
    Json.Document(
        Web.Contents(tenantUrl, [
            RelativePath = siteUrlPath & "/_api/web/GetFolderByServerRelativeUrl('" & targetFolderServerRelativeUrl & "')/Files",
            Headers = [Accept="application/json;odata=nometadata"]
            ]
        )
    ),

    extractServerRelativeUrlAndBuffer = List.Transform(getListOfFiles[value], each [ServerRelativeUrl])),

    retrieveContentForFile = (serverRelativeUrl as text) =>
        let 
            binaryContent = Web.Contents(tenantUrl, [
                RelativePath = siteUrlPath & "/_api/web/GetFileByServerRelativeUrl('" & serverRelativeUrl & "')/$value"
                ]
            ),
            // transformations here
            processedData = binaryContent
        in 
            processedData,

    combineAllFiles = Table.Combine(List.Transform(extractServerRelativeUrlAndBuffer, each retrieveContentForFile(_)))
in
    combineAllFiles

In short, you start by calling GetFolderByServerRelativeUrl on your target folder. This retrieves a list of up to 5,000 files. Please note that if you have more files than this, the request will fail as this specific endpoint does not support pagination.

From that response, you extract the ServerRelativeUrl for each file. Then, for every item in the list, you call the trusty GetFileByServerRelativeUrl endpoint. This returns the binary data, allowing you to perform any necessary transformations directly inside the loop.

If you want the best performance possible, you can experiment with Binary.Buffer or tweak Excel options like useHeaders or delayTypes. You can also try transforming the list into a table and retrieving the content via a new column, which may achieve higher concurrency. However, keep in mind that these are highly experimental optimizations and results may vary. But hey, at least you now know a manual alternative to the basic connector, and you can sound cooler in front of your colleagues.

Summary

For me personally, writing this blog post was a several-hour battle with the rendering and pagination logic of SharePoint.Files, all while trying to grasp the "why" behind it all. I hope you enjoyed this distilled version.

In conclusion, SharePoint.Contents with ApiVersion 15 is often simple enough. However, if you plan to perform any modifications based on file metadata, this custom solution can be faster, and you will learn a thing or two along the way.

Definitely keep an eye on this blog. I still intend to solve the challenge of bulk downloads from SharePoint because there must be a better way, even within the realm of Power Query.

Fix Slow Refreshes: Faster Way to Load SharePoint Files in Power BI