How to Add SharePoint File to Power BI

Connecting your SharePoint files to Power BI builds a direct bridge between your team's collaborative data and your analytical reports. This means no more manually downloading and re-uploading Excel files every Monday morning to refresh your dashboards. This guide will walk you through the most reliable methods for connecting both individual files and entire folders from SharePoint to Power BI.

Why Bother Connecting SharePoint to Power BI?

Before jumping into the "how," let's quickly cover the "why." If your team already uses Microsoft 365, SharePoint is likely a central hub for important files like sales trackers, marketing campaign results, or project management logs. Directly connecting these files to Power BI offers a handful of major advantages:

• A Single Source of Truth: Everyone works from the same file stored in a central, accessible location. No more wondering whose version of "Sales_Data_Final_v2.xlsx" is the right one.
• Automated Data Refreshes: Once connected, you can schedule your Power BI report to automatically refresh. Any updates made to the SharePoint file will flow directly into your dashboards, keeping your data current without any manual effort.
• Built-in Version Control: SharePoint's version history helps protect against accidental data loss or incorrect changes. Power BI will always pull from the latest saved version.
• Secure Data Management: Your organization’s SharePoint permissions are respected. Users can only access the data they’re authorized to see, keeping your analytics secure.

The Two Best Ways to Connect Your Data: File vs. Folder

There are two primary approaches for pulling data from SharePoint, and the one you choose depends on your goal:

1. Connecting to a Single File: This is perfect when you have one specific Excel or CSV file that acts as your primary "database." Think of a master budget tracker or an ongoing project plan that is constantly updated.
2. Connecting to a Folder: This method is incredibly powerful when you have multiple files with the same structure like monthly financial reports or weekly performance summaries saved as separate files. Power BI can automatically combine them into a single dataset.

Let's break down the step-by-step process for each method.

Method 1: Connecting a Single SharePoint File

This is a common task, but it comes with one quirky step that trips up many Power BI users. The key is to grab the correct path, not the one from your web browser’s address bar.

Step 1: Get the Correct File Path

Using the wrong links is the number one reason connections fail. Don’t copy the URL directly from your browser when the file is open in SharePoint Online. Power BI won’t be able to properly authenticate.

Instead, follow these steps:

1. Navigate to your SharePoint document library and find the file you want to connect.
2. Click the three dots (...) next to the file name and select Open > Open in app. This will open the file in the desktop version of Excel (or your relevant app).
3. Once the file is open, go to File > Info.
4. Click the Copy path button.

Now, paste this path into a text editor (like Notepad). It will look something like this:

https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/FolderName/YourFile.xlsx?web=1

The crucial part is to delete everything after the file extension. Your final, clean URL should look like this:

https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/FolderName/YourFile.xlsx

This is the link you'll use in Power BI.

Step 2: Connect Power BI Using the 'Web' Connector

Now, head over to Power BI Desktop.

1. On the Home ribbon, click Get Data and select Web.
2. A dialog box will appear. Paste your cleaned URL from Step 1 into the field.
3. Click OK.

Step 3: Authenticate Your Account

Power BI now needs permission to access your SharePoint account.

1. An authentication window will pop up. Select the Organizational account option.
2. Click Sign in and use your Microsoft 365 login credentials.
3. Once you've successfully signed in, click Connect.

Step 4: Load and Transform Your Data

After connecting, the Power BI Navigator window will appear, showing you the contents of your file. If it's an Excel workbook, you'll see a list of tables and sheets available.

• Select the specific sheet or table you want to import by checking the box next to its name. A preview will appear on the right.
• From here, you can either:

And that’s it! Your SharePoint file is now connected as a data source in your Power BI model. Any saved changes to the original file will show up the next time you refresh your report.

Method 2: Connecting an Entire SharePoint Folder

This method saves a massive amount of time if you collect data in separate but similarly structured files - for example, a new sales report for each month. Power BI can consolidate them automatically.

Step 1: Get Your SharePoint Site URL

This path is much simpler to get. Navigate to the homepage of the SharePoint site where your folder resides and copy the URL from your browser. We only need the root site path.

For example, if your folder is at https://yourcompany.sharepoint.com/sites/MarketingReports/Shared%20Documents/..., just grab this part:

https://yourcompany.sharepoint.com/sites/MarketingReports

Step 2: Connect Using the 'SharePoint Folder' Connector

In Power BI Desktop:

1. Go to Get Data and search for "SharePoint."
2. Select SharePoint folder and click Connect.
3. In the dialog box, paste your SharePoint site URL from Step 1 and click OK.

You may be asked to authenticate with your Organizational account just as in the previous method.

Step 3: Combine Your Files in Power Query

Instead of the Navigator, Power BI will show a preview of all files and folders available on that SharePoint site. It's best to handle the filtering and combining steps inside Power Query.

1. Click Transform Data to open the Power Query Editor.
2. You'll see a table listing every item with metadata like Name, Extension, Date modified, and Folder Path. Use the filter arrows on the column headers to narrow down to the files you need. Two common filtering steps are:
3. Once you have a filtered list of the files you want to combine, find the column named Content (which will show "Binary" as the type). Click the Combine Files icon (two downward arrows) in the header of this column.
4. A new window will appear. Power BI will ask you to select a sample file as a template for what to extract from each file. Choose the relevant sheet or table (e.g., Sheet1) and click OK.

Power Query works its magic behind the scenes, creating a series of helper queries and steps. The end result is a single table containing all the data from every file in that folder.

Step 4: Perform Final Transformations and Load

Your combined data is now ready. You can perform any additional cleaning steps needed - like changing data types or removing unneeded columns. When you're done, click Close & Apply on the Home ribbon.

Now, whenever you add a new file (say, Sales_April-2024.xlsx) to that SharePoint folder, simply refreshing your Power BI data will automatically append the new data to your dataset.

Final Thoughts

Learning to connect Power BI directly to SharePoint files is a serious level-up for anyone creating automated reports. It builds a reliable data pipeline that saves countless hours of manual work and ensures your dashboards always reflect the latest information from your team’s collaborative efforts.

While mastering Power BI is a valuable skill, it often comes with a steep learning curve spent juggling complex settings and connectors. We created Graphed because we believe getting insights shouldn't require so much friction. Rather than wrangling data source settings, you can connect your marketing and sales tools in a few clicks, and then build real-time dashboards just by describing what you want to see in simple, human language. It turns the entire report-building process into a quick conversation, letting you focus on making decisions, not debugging connections.