Connecting to SharePoint Lists in Power BI: A Detailed Guide
Power BI enables users to connect to SharePoint lists to extract, transform, and visualize data. SharePoint Lists are commonly used to store structured data in organizations, and integrating them with Power BI allows businesses to create interactive dashboards and reports.
This guide provides a step-by-step explanation of how to connect Power BI to SharePoint Lists, covering different methods, authentication options, data transformation, and best practices.
Step 1: Understanding SharePoint Lists as a Data Source
Before we connect to SharePoint Lists in Power BI, let’s understand what they are:
- SharePoint Lists act as structured repositories similar to databases. They contain columns (fields) and rows (items).
- SharePoint Lists can store various types of data, such as text, numbers, choices, dates, and attachments.
- Power BI supports both Online (SharePoint Online) and On-Premises SharePoint Lists (SharePoint Server).
Step 2: Pre-requisites Before Connecting to SharePoint Lists
Before establishing the connection, ensure you have:
- Power BI Desktop Installed – Download and install the latest version of Power BI Desktop.
- SharePoint Site URL – You need the root URL of your SharePoint site (e.g.,
https://yourcompany.sharepoint.com/sites/YourSite). - Necessary Permissions – You must have at least Read Access to the SharePoint list you want to connect to.
- Internet Connectivity – If using SharePoint Online, a stable internet connection is required.
- Power BI Gateway (Optional) – If connecting to an on-premises SharePoint List, you may need an On-Premises Data Gateway.
Step 3: Connecting Power BI to SharePoint Online List
To import data from a SharePoint Online List, follow these steps:
1. Open Power BI Desktop
- Launch Power BI Desktop.
- Click on Home → Get Data.
2. Select SharePoint Online List as Data Source
- In the Get Data window, search for SharePoint Online List.
- Select SharePoint Online List and click Connect.
3. Enter SharePoint Site URL
- A prompt will ask for the SharePoint site URL.
- Enter the root URL of the site (not the list URL).
- ✅ Example:
https://yourcompany.sharepoint.com/sites/YourSite - ❌ Do not enter the full list URL like
https://yourcompany.sharepoint.com/sites/YourSite/Lists/ListName
- ✅ Example:
4. Choose Authentication Method
Power BI offers different authentication methods:
- Microsoft Account (Organizational) – If using SharePoint Online with Office 365, sign in with your organizational Microsoft credentials.
- Windows Authentication – For on-premises SharePoint Server (requires Power BI Gateway).
- Anonymous/Public – Only works for public SharePoint sites (rarely used).
Select Microsoft Account, sign in, and click Connect.
5. Select the SharePoint List
- Power BI will display all available SharePoint Lists from the site.
- Select the desired list(s) you want to import.
- Click Load to import the data into Power BI.
💡 Tip: Click Transform Data instead of Load if you want to clean and shape the data before importing it.
Step 4: Transforming and Cleaning SharePoint List Data (Power Query)
Once the data is loaded into Power Query Editor, you can clean and shape it before using it in reports.
1. Remove Unwanted Columns
SharePoint Lists may contain unnecessary system-generated columns.
- In Power Query, select columns and click Remove Columns.
2. Rename Columns
- Double-click column names to rename them for better readability.
3. Change Data Types
- Ensure columns have the correct data types (e.g., Date, Number, Text).
4. Expand Lookup Fields (Optional)
If your SharePoint List contains Lookup Columns, expand them to retrieve related data:
- Click on the expand icon next to the column name.
- Select the fields you want to extract.
5. Remove Duplicates or Null Values
- Use Remove Duplicates or Remove Blank Rows under the Transform tab.
After cleaning the data, click Close & Apply to load it into Power BI.
Step 5: Connecting to SharePoint On-Premises List
For SharePoint Server (On-Premises), the connection process differs slightly:
1. Open Power BI Desktop
- Click Home → Get Data.
2. Select SharePoint List as Data Source
- Search for SharePoint List (without “Online”).
- Click Connect.
3. Enter SharePoint Site URL
- Use the on-premises SharePoint site URL (e.g.,
http://yourcompany-internal).
4. Configure Authentication
- Choose Windows Authentication and enter your credentials.
5. Install Power BI Gateway (Optional)
If you want to refresh data automatically, install and configure the On-Premises Data Gateway.
Follow the same data transformation steps as before.
Step 6: Creating Reports and Dashboards
Once the SharePoint List data is loaded into Power BI, you can:
1. Create Visuals
- Use bar charts, tables, slicers, and KPIs to analyze the data.
2. Add Filters and Slicers
- Use filters and slicers to interactively analyze data.
3. Publish to Power BI Service
- Click Publish to upload the report to the Power BI cloud.
4. Schedule Data Refresh
- Go to Power BI Service → Datasets → Refresh Settings.
- Configure the refresh frequency (daily, hourly, etc.).
Step 7: Best Practices for Using SharePoint Lists in Power BI
- Optimize Performance – Avoid loading unnecessary columns and rows.
- Use Incremental Refresh – For large lists, configure incremental refresh to improve performance.
- Handle Permissions Carefully – Ensure appropriate user permissions in SharePoint.
- Use Dataflows for Reusability – Store SharePoint data in a Power BI Dataflow for multiple reports.
- Automate Data Refresh – Set up scheduled refresh for live updates.
Conclusion
Connecting Power BI to SharePoint Lists allows organizations to visualize structured data from SharePoint in dynamic dashboards and reports. By following the steps outlined in this guide, you can successfully:
- Connect to both SharePoint Online and On-Premises SharePoint Lists.
- Transform and clean SharePoint List data using Power Query.
- Build powerful reports and schedule automatic data refresh.
This integration helps businesses track workflows, manage data efficiently, and gain insights from SharePoint List information. 🚀
Keywords and Tags
Power BI, SharePoint Lists, Power BI SharePoint Connection, Business Intelligence, Data Sources, Power Query, Power BI Online, Power BI Desktop, SharePoint Data Integration, Power BI Reports, SharePoint Online, Power BI Gateway, On-Premises SharePoint, Power BI Data Refresh, Data Visualization, Data Analytics, Microsoft Power BI