SharePoint.Contents
Accessing DataReturns a navigation table of sites, lists, and document libraries from a SharePoint site.
Syntax
SharePoint.Contents(url as text, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
url | text | Yes | The URL of the SharePoint site (e.g., "https://contoso.sharepoint.com/sites/Finance"). |
options | record | No | An optional record with options. Supports ApiVersion to specify the SharePoint REST API version. |
Return Value
table — A navigation table where each row represents a list, library, or subsite available at the SharePoint site URL.
Remarks
SharePoint.Contents connects to a SharePoint Online or SharePoint on-premises site and returns a navigation table of all lists, document libraries, and subsites available at that URL. It is the most general-purpose SharePoint connector, exposing the full site structure as a hierarchical navigation table. For production queries, consider using SharePoint.Files (for files in document libraries) or SharePoint.Tables (for structured SharePoint list data), which are purpose-built and simpler to navigate.
Navigation table structure: The result is a hierarchical table where each row represents a list, library, or subsite. Each row includes a Name column and a Data column containing a nested navigation table for that item. Document libraries show file metadata and binary content; lists show list items; subsites expose their own nested structure.
Authentication: Microsoft Account (OAuth 2.0) for SharePoint Online. Windows or Basic credentials for SharePoint on-premises. Configure credentials in the Power Query data source settings dialog using the site root URL as the credential scope. If you connect to multiple SharePoint sites in the same tenant, they typically share the same credential when the root domain matches.
URL format: Use the site collection root URL (e.g., https://contoso.sharepoint.com/sites/Finance) rather than a subsite URL or library URL. The connector enumerates content downward from the URL you provide. Using the tenant root (https://contoso.sharepoint.com) will enumerate all site collections, which may be very slow.
On-premises SharePoint: Fully supported with Windows authentication and an on-premises data gateway for Power BI Service scheduled refresh.
Key option:
- ApiVersion (number) — the SharePoint REST API version. Defaults to 15 (SharePoint 2013 and later). Set to 14 for SharePoint 2010.
Query folding: Limited. Some simple row filters may fold to the SharePoint REST API, but most transformations are performed in Power Query after data retrieval.
Examples
Example 1: Browse all lists and libraries on a SharePoint site
```powerquery
SharePoint.Contents("https://contoso.sharepoint.com/sites/Finance")Example 2: Navigate to a specific document library by name
```powerquery
let
Site = SharePoint.Contents("https://contoso.sharepoint.com/sites/Finance"),
Documents = Site{[Name="Documents"]}[Data]
in
DocumentsExample 3: List the names of all lists and libraries on a site
```powerquery
let
Source = SharePoint.Contents("https://contoso.sharepoint.com/sites/Finance"),
Names = Source[Name]
in
NamesExample 4: Connect to a SharePoint on-premises site with explicit API version
```powerquery
SharePoint.Contents("https://sharepoint.contoso.local/sites/HR", [ApiVersion = 15])