Configuring And Connecting SharePoint As A Data Source

Configuring and Connecting SharePoint as a Data Source

SharePoint is one of the most versatile data sources in the Microsoft ecosystem. It powers Power BI dashboards, Power Apps, and custom integrations via Microsoft Graph and the REST API. This guide covers every connection method — SharePoint Lists, Document Libraries, OData, Dataflows, and Incremental Refresh — with step-by-step setup for each.

Key facts

• SharePoint Online data is accessible via the SharePoint Online connector, Microsoft Graph API, SharePoint REST API, and OData feed.
• Power BI connects to SharePoint lists in both Import and DirectQuery modes.
• SharePoint throttles API calls at high volume — Incremental Refresh and Dataflows reduce throttling risk.
• Authentication uses organizational accounts (OAuth) or service principal credentials for automated pipelines.
• SharePoint lists support up to 30 million items, but filtering and indexing are required beyond 5,000 items (the list view threshold).

SharePoint Data Source Types

• SharePoint Lists — Structured, tabular data with defined columns, lookups, and calculated fields. Maps cleanly to relational data models.
• Document Libraries — Metadata-rich file repositories where column values, content types, and managed metadata can be queried as data points.
• Microsoft Graph API — Unified REST endpoint for accessing SharePoint sites, lists, drives, and items programmatically with granular permissions.
• SharePoint REST API — OData-based access to list data, site properties, and user information with filtering, sorting, and pagination support.
• SharePoint Online Connector — Pre-built connectors in Power BI, Power Automate, and Power Apps that abstract API complexity for business analysts.

Connecting SharePoint to Power BI

SharePoint Online List Connector

This is the simplest method for most reporting scenarios.

• In Power BI Desktop: Get Data → SharePoint Online List → enter your site URL.
• Authenticate with your organizational account.
• Choose Import mode for scheduled refresh or DirectQuery for live data.
• Select the lists you need and apply Power Query transformations.

SharePoint Folder Connector

Use this to aggregate multiple Excel or CSV files from a document library.

• Get Data → SharePoint Folder → enter the document library URL.
• Power BI loads all files in the folder and combines them into one table.
• Best for consolidating departmental reports that share the same schema.

OData Feed

Use SharePoint's REST API endpoint as an OData source for advanced filtering.

• Endpoint format: https://tenant.sharepoint.com/sites/yoursite/_api/web/lists
• Supports custom query parameters for server-side filtering before data lands in Power BI.
• Reduces data transfer volume for large lists.

Power BI Dataflows

For reusable, governed data pipelines, use Dataflows instead of direct connectors.

• Create a Dataflow in the Power BI service that extracts SharePoint data.
• Apply Power Query transformations and store results in Azure Data Lake.
• Multiple reports reuse the same Dataflow instead of each querying SharePoint directly.
• Reduces API call volume and throttling risk significantly.

Incremental Refresh

For large SharePoint lists, Incremental Refresh is mandatory for performance. It loads only new or modified records rather than the full list.

• Requires a DateTime column that tracks when records were created or modified.
• Configure the Incremental Refresh policy in Power BI Desktop before publishing.
• Target: refresh only the last 1–7 days of data; keep 12–36 months in the dataset.

Connecting SharePoint to Power Apps and Power Automate

• Power Apps SharePoint connector — Connect to SharePoint lists as a data source. Build forms, galleries, and edit screens that read and write list items.
• Power Automate SharePoint trigger — Trigger flows when items are created, modified, or deleted in a SharePoint list.
• Power Automate SharePoint action — Create, update, get, and delete SharePoint list items from automated workflows.
• Site-level permissions — Power Apps and Power Automate inherit SharePoint permissions. Users can only read or write items they have access to in SharePoint.

Authentication and Security Configuration

• Organizational account (OAuth) — Default for interactive Power BI reports. The report runs under the signed-in user's identity. Row-level access matches SharePoint permissions.
• Service principal — Required for scheduled refresh and automated pipelines. Register an Azure AD app with SharePoint site permissions. More secure than storing user credentials.
• Gateway requirement — SharePoint Online connections from the Power BI service do not require a data gateway. On-premises SharePoint requires an on-premises data gateway.
• Sensitivity labels — Apply Microsoft Purview sensitivity labels to reports built on SharePoint data. Labels restrict download, print, and sharing based on data classification.

Performance Optimization and Throttling

SharePoint throttles API calls aggressively at high volume. These techniques reduce throttling risk.

• Server-side filtering — Filter data in the OData query string rather than pulling all rows into Power BI for client-side filtering.
• Column selection — Select only the columns you need. Large column sets increase payload size and throttling risk.
• Indexed columns — Index all filter and sort columns in SharePoint before using them in Power Query or OData queries.
• Stagger refresh schedules — Avoid refreshing multiple datasets from the same SharePoint site simultaneously.
• Dataflows as intermediary — Route SharePoint data through a Dataflow first. Reports query the Dataflow (Azure Data Lake), not SharePoint directly.

Frequently Asked Questions

How do I connect Power BI to a SharePoint list?

In Power BI Desktop, click Get Data → SharePoint Online List. Enter the SharePoint site URL (not the list URL). Sign in with your organizational account. Select the lists to import and apply any Power Query transformations.

What is the difference between SharePoint REST API and Microsoft Graph API?

The SharePoint REST API directly accesses SharePoint-specific resources (lists, sites, document libraries). Microsoft Graph is a unified API that accesses SharePoint alongside OneDrive, Teams, and other Microsoft 365 services. Graph is preferred for new development; the REST API is useful for SharePoint-only scenarios.

How do I handle SharePoint throttling in Power BI?

Use server-side OData filtering to reduce row counts before data lands in Power BI. Set up Incremental Refresh for large lists. Route data through a Power BI Dataflow to decouple report refresh from SharePoint API calls.

Can Power BI use DirectQuery with SharePoint?

Yes. The SharePoint Online List connector supports DirectQuery mode. This queries SharePoint live on each report interaction. It is suitable for lists under 5,000 rows. For larger lists, Import mode with Incremental Refresh gives better performance.

Do I need a data gateway for SharePoint Online?

No. SharePoint Online connections in the Power BI service do not require a gateway. A gateway is only required for on-premises SharePoint (SharePoint Server 2016 or 2019).

Configure SharePoint as a Data Source

EPC Group has completed 6,500+ SharePoint implementations and 1,500+ Power BI deployments. Talk to a data integration architect about your setup. Call (888) 381-9725 or request a consultation.