Case Study: Connecting SharePoint Lists in Power BI

Context

The client’s team was using SharePoint Lists to track approvals, tasks, and project updates.

Unlike files stored in document libraries, these lists are essentially like online tables — with rows and columns managed directly inside SharePoint.

They wanted these lists to connect live into Power BI for reporting dashboards.

The Problem

Teams initially treated lists like files:

• Some exported the list to Excel every week and reloaded into Power BI.

• Others tried copy-paste methods.

• Refresh automation was completely missing.
  

This led to manual effort, outdated data, and inconsistent reports.

The Smart Fix: SharePoint List Connector

Instead of files, we connected directly to the SharePoint List API using the dedicated Power BI connector.

Step-by-Step Process to connect to SharePoint Lists

Step 1 – Get the Site URL

• Go to the SharePoint site where the list is hosted.

• Copy the root site URL (not the list URL).
  

Step 2 – Use SharePoint List Connector

• In Power BI → Get Data → More → Online Services → SharePoint Online List.
  

  

• Paste the site URL.
  

  

Step 3 – Authentication

• Sign in with Organizational Account.

• Ensure you have the right permissions to view/edit the list.
  

Step 4 – Choose the List

• Power BI will show all available lists in that site.

  

• Select the required one(s).
  

Step 5 – Clean the Metadata

• SharePoint lists come with many system columns (Author, GUID, Attachments, Modified By, etc.).

• Keep only business-relevant fields (Task Name, Status, Date, Owner, etc.).

  
  

  

Step 6 – Transform & Model

• Rename columns to meaningful names.

• Apply data types.

• Load into your model for reporting.

  
  

  

Pro Tips

• If your list contains lookups (e.g., a dropdown referencing another list), Power BI shows it as a nested table. Use Expand Column to flatten it.

• For large lists, performance may slow down. Filter at source where possible.

• Always rename queries clearly (Z Source – SharePoint List – Tasks).

Takeaway

SharePoint Lists are not files — they are structured tables with metadata.

To use them in Power BI effectively:

• Connect with the SharePoint Online List connector.

• Strip away unnecessary system columns.

• Expand lookup fields properly.
  

This makes your dashboard live, automated, and always up to date with the latest list entries.