SharePoint.Tables
Accessing DataReturns a navigation table of SharePoint list data from the specified SharePoint site.
Syntax
SharePoint.Tables(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, and ViewColumns to select specific columns. |
Return Value
table — A navigation table where each row represents a SharePoint list, with a Data column containing the list items as a table.
Remarks
SharePoint.Tables connects to a SharePoint site and returns a navigation table of all SharePoint lists available at that URL. Each row in the navigation table represents one list; drilling into the Data column returns the list's items as a Power Query table. This is the preferred function for reading structured data from SharePoint lists (custom lists, task lists, issue trackers, etc.). For files stored in document libraries, use SharePoint.Files instead.
Navigation table and list columns: The top-level result contains Name and Data columns. Inside each list's Data, columns correspond to SharePoint list fields. SharePoint list columns have complex types that may not surface as simple scalar values:
- Text, Number, Date, Boolean fields → returned as M text, number, datetime, or logical values.
- People or Group fields → returned as records containing display name, email, and user ID. Expand with Table.ExpandRecordColumn.
- Lookup fields → returned as records with Value and Id fields. Expand to get the lookup value text.
- Managed Metadata (Taxonomy) fields → returned as records. Expand to get the term label.
- Multi-value fields → returned as lists of records.
Authentication: Microsoft Account (OAuth 2.0) for SharePoint Online. Windows or Basic credentials for SharePoint on-premises. Configure credentials using the site root URL as the credential scope.
Query folding: Partial. Simple row filters on list column values may fold to the SharePoint REST API as $filter query parameters. Complex transformations occur in Power Query after data retrieval.
Pagination: The SharePoint REST API returns up to 5,000 items per page by default. SharePoint.Tables automatically follows continuation links to retrieve all items from large lists.
Power BI Service: SharePoint Online lists are supported for direct cloud refresh using Microsoft Account authentication — no gateway is required. SharePoint on-premises requires an on-premises data gateway.
Key option:
- ApiVersion (number) — SharePoint REST API version. Defaults to 15 (SharePoint 2013+). Set to 14 for SharePoint 2010.
Examples
Example 1: Browse all SharePoint lists on a site
```powerquery
SharePoint.Tables("https://contoso.sharepoint.com/sites/Finance")Example 2: Access a specific SharePoint list by name
```powerquery
let
Source = SharePoint.Tables("https://contoso.sharepoint.com/sites/Finance"),
BudgetList = Source{[Name="Budget Tracker"]}[Data]
in
BudgetListExample 3: Expand a Lookup column from a SharePoint list
```powerquery
let
Source = SharePoint.Tables("https://contoso.sharepoint.com/sites/Finance"),
Projects = Source{[Name="Projects"]}[Data],
Expanded = Table.ExpandRecordColumn(Projects, "Category", {"Value"}, {"Category.Value"})
in
ExpandedExample 4: Filter list items after retrieval
```powerquery
let
Source = SharePoint.Tables("https://contoso.sharepoint.com/sites/Finance"),
Tasks = Source{[Name="Tasks"]}[Data],
OpenTasks = Table.SelectRows(Tasks, each [Status] = "In Progress")
in
OpenTasksExample 5: Connect to a SharePoint on-premises list
```powerquery
let
Source = SharePoint.Tables("https://sharepoint.contoso.local/sites/HR", [ApiVersion = 15]),
EmployeeList = Source{[Name="Employees"]}[Data]
in
EmployeeList