SharePoint.Files vs SharePoint.Contents in Power Query: Which One Should You Use?
- Admin
- Nov 9
- 6 min read
Overview
When you connect Power Query / Power BI to a SharePoint site (or document library), there are (at least) two common M-functions you may see or use:
SharePoint.Files(siteUrl as text, optional options as record) as tableSharePoint.Contents(siteUrl as text, optional options as record) as tableAlthough they sound similar, their behavior, performance, metadata returned, and navigation experience differ. Understanding their differences helps in building more robust and performant queries.
Below I walk through how each works, compare them, and show example use patterns and caveats.
1. How SharePoint.Files works
1.1 Behavior
SharePoint.Files is designed to enumerate all files (and typically some metadata) under a given SharePoint site or library (and its subfolders).
It fetches metadata for every document across the entire site (or library) — which can be heavy.
The UI “SharePoint folder” connector in Power BI / Excel often generates a query using SharePoint.Files(...) under the hood.
It returns a table with columns such as Content (binary), Name, Extension, Date modified, Date created, Attributes, Folder Path, etc.
1.2 Advantages
Straightforward when your site or document library is small or modest in file count.
Good when you want to “combine files” (e.g. many Excel/CSV files in a folder tree) because you already have the binary content column.
You get all file-level metadata (e.g. file size, folder path, extension) in one go.
1.3 Drawbacks / limitations
Performance issues at scale: Because it loads metadata for every file in the site, for large sites or libraries (hundreds or thousands of files), it can become very slow, or even time out.
It might fetch more data than you need, making filtering less efficient unless done early.
It may not include certain SharePoint-specific metadata (custom library columns) that were defined in the site’s document library schema.
2. How SharePoint.Contents works
2.1 Behavior
SharePoint.Contents(siteUrl, optional options) returns a table listing folders and files as structured content under the site.
It behaves more like a folder-navigation model: you can expand into subfolders via the Content column (which holds a table) and navigate down.
Because it doesn’t immediately flatten everything, it may enumerate less metadata up front (thus lighter).
2.2 Advantages
Better performance for large SharePoint sites: Because it avoids pulling metadata everywhere at once, it can be faster in larger scenarios.
More “exploratory” / hierarchical navigation: you see folder structure and can drill into where your files are, rather than being overwhelmed by a huge flat list.
Helps reduce initial load overhead; you can filter earlier (e.g. navigate only into one relevant folder).
Because it returns fewer columns by default, you may avoid overfetching irrelevant metadata.
2.3 Drawbacks / caveats
Authentication / permission issues: Some users report that SharePoint.Contents fails to authenticate in scenarios where SharePoint.Files works fine—especially when users lack access to the entire site (only to a subfolder).
Missing library-specific metadata: by default, SharePoint.Contents does not retrieve additional columns (e.g. custom metadata fields you set in the library) unless you augment via other queries (like OData) and join.
Some combination / “Combine Files” steps might fail due to how binary content is surfaced, especially when using newer API versions.
Slightly more manual navigation work is needed (you navigate into subfolders, expand content tables) vs the flattening one-stop approach of SharePoint.Files.
3. Side-by-side comparison
Feature / dimension | SharePoint.Files | SharePoint.Contents |
Initial flattening | Returns a flat list of all files under the site/library (with binary content) | Returns a hierarchical folder + file tree; you navigate via Content column |
Metadata richness | Rich file metadata (size, attributes) | Basic file/folder metadata; custom library columns not included automatically |
Performance | Can degrade severely for large sites (many files) | Often faster because less upfront enumeration |
Filtering strategy | Important to filter early (e.g. by folder path or extension) to avoid heavy load | Better suited for filtering at folder level |
Navigation experience | Direct file level list; less intuitive for structure | Natural drill-down structure |
Authentication / permissions | Tends to work when access is limited to subfolder | May fail if you don’t have access to the full site or parent levels |
Combine Files support | Works well with “Combine Files” (e.g. Excel files) | Sometimes “Combine Files” errors occur depending on API version / content format |
Custom metadata / library columns | Doesn’t surface custom columns either (same limitation) | Even more limited in default metadata; must use supplementary queries (OData) |
4. When to use which (recommendations)
Here are guidelines and scenarios for choosing one over the other:
Small sites or limited number of files: Use SharePoint.Files for simplicity and ease, because performance is acceptable and you get file-level content and metadata easily.
Large libraries / many files / performance concerns: Start with SharePoint.Contents, drill down to the folder you care about (e.g. using Source{[Name="Documents"]}[Content] or similar), then filter and expand. This avoids loading all file metadata at once.
When you need to access file binary content (for combining CSV/Excel files), either approach can work, but using Contents with navigation to the relevant folder may help avoid overhead. But test the “Combine Files” step carefully (some users observed SharePoint.Contents with API version 15 having issues).
When you need custom metadata columns (from library): Neither function gives custom library columns directly; in those cases, use a hybrid: use SharePoint.Contents (or Files) to get file structure and content, then use OData.Feed or SharePoint list / library API to fetch the library metadata and merge.
When your access is limited: If your user account only has access to a subfolder, SharePoint.Files might still work, while SharePoint.Contents could fail due to required father-level access.
In many real-world cases, developers use a combination approach: use Contents for structure + navigation + filtering, then possibly switch to Files or use an auxiliary query (OData) to fetch additional metadata, and merge.
5. Sample code / patterns
Here are simplified M-code snippets illustrating usage.
5.1 Using SharePoint.Files
let
siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite",
allFiles = SharePoint.Files(siteUrl, [ApiVersion=15]),
filtered = Table.SelectRows(allFiles, each Text.EndsWith([Extension], ".xlsx") and Text.Contains([Folder Path], "Reports")),
// Expand or combine as needed
// e.g. filter out nested folders, combine binaries etc.
result = filtered
in
result
5.2 Using SharePoint.Contents with navigation
let
siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite",
root = SharePoint.Contents(siteUrl, [ApiVersion=15]),
// Navigate into the library or folder (e.g. “Documents”)
docLib = root{[Name = "Shared Documents"]}[Content],
// Now filter to subfolders or files
sub = docLib{[Name = "Reports"]}[Content],
files = Table.SelectRows(sub, each [Extension] = ".xlsx"),
// Add full URL or binary content, etc.
result = files
in
result
You can wrap this logic into a parameterized helper function so you pass the folder path and it navigates and returns just the files you care about. Many practitioners do that to hide the complexity and reuse it.
5.3 Hybrid pattern to get custom metadata
A more advanced pattern (based on public community solutions) is:
Use SharePoint.Contents (or SharePoint.Files) to get file list + binary where needed.
Use OData.Feed or the SharePoint REST API (_api) to fetch library metadata (custom columns).
Merge / join those tables based on file URL or path.
Example skeleton (simplified):
let
siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite",
// part A: file list
fileList = let
root = SharePoint.Contents(siteUrl, [ApiVersion=15]),
lib = root{[Name="Shared Documents"]}[Content],
// flatten to files
filesTable = Table.SelectRows(lib, each [Attributes][Kind] <> "Folder")
in
filesTable,
// part B: metadata via REST / OData
meta = OData.Feed(siteUrl & "/_api/web/lists/getbytitle('Shared Documents')/items", null, [Implementation="2.0"]),
cleanMeta = Table.SelectColumns(meta, {"FileLeafRef", "CustomColumn1", "CustomColumn2"}),
// join
merged = Table.NestedJoin(fileList, {"Name"}, cleanMeta, {"FileLeafRef"}, "Meta", JoinKind.LeftOuter),
expanded = Table.ExpandTableColumn(merged, "Meta", {"CustomColumn1", "CustomColumn2"}, {"Custom1", "Custom2"})
in
expanded
This gives you file content + your custom metadata in one result table. But be careful with refresh performance and paging.
6. Common pitfalls & tips
Always do filtering or navigation as early as possible (i.e. before expanding many rows) to reduce load and speed up.
Be wary of API version settings (14 vs 15). Some users reported SharePoint.Contents combined file errors only when using version 15.
Avoid including too many irrelevant files (e.g. non-Excel or non-CSV) — filter by extension early.
If you see authentication errors using SharePoint.Contents but Files works, check your permissions (you might lack parent site permissions).
Be careful with file names containing special characters (#, %, $, etc) — they can break paths or URIs.
For huge libraries, break into smaller scopes (dedicated folders/sites) where possible.
Be prepared to use hybrid queries (merge from two sources) when neither function alone gives everything you need.
7. Summary & recommendation
SharePoint.Files is easier and more straightforward when your file set is small to moderate; you get all file metadata and binary content in one go.
SharePoint.Contents is typically more performant in larger environments and provides a cleaner folder-navigation model, but lacks automatic rich metadata and occasionally faces authentication permissions issues.
In practice, many robust solutions use a hybrid approach: use Contents for structure and filtering, then optionally fetch metadata via REST or OData, and merge.
Always test in your specific SharePoint environment (file counts, permissions, refresh behavior) because theoretical performance gains may differ in real cases.
👉 The key is to filter early, avoid loading unnecessary files, and test performance in your environment.
Do you need a starter Power Query package in every Power BI or Excel project? Just with a slight adjustment to the quick and efficient calculations. Discover these must-have custom functions.
Comments