Case Study: Connecting SharePoint Files and Folders in Power BI

archit032
Sep 27
2 min read

Context

The client stored all their Excel and CSV files on SharePoint Online instead of a local folder.

They wanted the same automation as with folder connectors — whenever a new file was added in SharePoint, Power BI should pick it up without manual uploads.

The Problem

Teams were struggling because:

Many tried using the SharePoint site URL directly, which didn’t work properly.
Some copied file links instead of folder links — breaking refreshes.
Non-technical users couldn’t figure out how to point Power BI to the right document library + folder.

This caused broken connections and failed refreshes whenever new files were added.

The Smart Fix: SharePoint Folder Connector

We used the SharePoint Folder connector in Power BI to establish a stable and scalable connection.

Step-by-Step Process to connect to SharePoint Folder

Step 1 – Get the Site URL

Go to your SharePoint site in the browser.
Copy only the root site URL (e.g., https://companyname.sharepoint.com/sites/ProjectX).

Step 2 – Use SharePoint Folder Connector

In Power BI, select Get Data → SharePoint Folder.
Paste the site URL (not the file URL).

Step 3 – Authentication

Choose Organizational Account → Sign in.
This ensures Power BI uses your Office 365 credentials securely.

Step 4 – Transform Data

Power BI lists all files in that site.
Use the Folder Path column to filter down to the exact subfolder you want.
Keep only relevant columns (Name, Folder Path, Content, Date Modified, etc.).

Step 5 – Reference & Organize

Create a Z Source – SharePoint query.
Split Folder Path by delimiter (/) to easily filter specific folders.
Reference this for your fact tables.

Step 6 – Expand the Files

Drill into the Content column to read the files.

Pro Tips

Always filter by Folder Path, not just File Name — this keeps refresh stable.
Group all SharePoint queries into a Sources folder for easy navigation.
If performance slows, disable load for helper queries.