How to Connect Power BI to SharePoint: Step-by-Step Guide

To connect Power BI to SharePoint, use the Get Data option and select SharePoint Folder or SharePoint Online List depending on your data source. Enter your SharePoint site URL, authenticate if needed, and then load the data into Power BI for visualization.

Syntax

Power BI offers two main ways to connect to SharePoint data:

• SharePoint Folder: Connects to files stored in SharePoint document libraries.
• SharePoint Online List: Connects directly to SharePoint lists as tables.

In Power BI Desktop, use Get Data > SharePoint Folder or Get Data > SharePoint Online List. You must provide the SharePoint site URL (not the full file path) and authenticate with your credentials.

powerquery

let
    Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite", [ApiVersion = 15])
in
    Source

Example

This example shows how to connect to a SharePoint document library and list all files using Power Query M code in Power BI Desktop.

powerquery

let
    Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx"))
in
    FilteredFiles

Output

A table listing all Excel files (.xlsx) in the specified SharePoint document library with columns like Name, Folder Path, Date modified.

Common Pitfalls

• Using full file URL instead of site URL: Always use the SharePoint site URL, not the full file path.
• Authentication issues: Ensure you have proper permissions and use organizational account login when prompted.
• Wrong API version: Use ApiVersion = 15 for SharePoint Online to avoid connection errors.
• Data privacy settings: Set data privacy to organizational or public to prevent refresh errors.

powerquery

/* Wrong way: Using full file URL */
let
    Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents/file.xlsx")
in
    Source

/* Right way: Using site URL only */
let
    Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite", [ApiVersion = 15])
in
    Source

Quick Reference

Step	Action	Notes
1	Open Power BI Desktop	Start your report project
2	Get Data > SharePoint Folder or SharePoint Online List	Choose based on your data type
3	Enter SharePoint site URL	Example: https://yourcompany.sharepoint.com/sites/yoursite
4	Authenticate with organizational account	Use your company login
5	Select files or lists to load	Preview and choose data
6	Load data and build visuals	Start creating reports

Key Takeaways

Use the SharePoint site URL, not the full file path, when connecting in Power BI.

Choose 'SharePoint Folder' for files and 'SharePoint Online List' for lists.

Authenticate with your organizational account to access SharePoint data.

Set data privacy settings correctly to avoid refresh errors.

Use Power Query to filter and transform SharePoint data before loading.