How to Get Data from a SharePoint Folder in Power BI

Connecting Power BI to a single Excel file is simple, but what happens when you have a folder full of them? Manually combining sales reports from every month or performance data from every team is a recipe for wasted hours and inevitable errors. This guide will walk you through how to connect Power BI directly to a SharePoint folder, a powerful technique that automatically combines all your files into a single, clean dataset that's ready for analysis and stays up-to-date.

Why Bother Connecting to a SharePoint Folder?

You might be thinking, "Can't I just combine the files on my desktop?” You could, but connecting directly to a SharePoint folder offers some serious advantages that create a much more automated and reliable reporting workflow.

• A Single Source of Truth: Everyone on your team can access and add files to the same central location. You're always working from the most current data, eliminating the confusion of multiple file versions saved across different computers.
• Automatic Refreshes: Once you set up the connection, Power BI handles the rest. Drop a new sales report for April into the folder, hit refresh, and Power BI will automatically pick it up, apply all your cleansing steps, and add it to your dataset. No more manual copy-pasting.
• Scalability: This method works flawlessly whether you have three files or three hundred. As your business grows and your data volume increases, your Power BI report will scale with you without requiring any changes to your process. It makes your reporting process future-proof.

First, Grab Your SharePoint Site URL

This is the most common place where people run into problems, but it's an easy fix. Power BI doesn't need the URL of the specific folder, it needs the URL of the main SharePoint site that the folder lives in. Using the wrong URL is the number one cause of connection errors.

Here’s how to find the correct one:

1. Navigate to your SharePoint site in your web browser.
2. Look at the URL in the address bar. You want to copy only the first part of the URL, up to the site name.

For example, if the full path to your folder looks like this:

https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FMarketing%2FShared%20Documents%2FMonthly%20Reports

You only need this part:

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

Essentially, you’re trimming everything after the site name (in this case, "Marketing"). Keep this root URL handy - you’ll need it in the next step.

Connecting Power BI to the SharePoint Folder: A Step-by-Step Guide

With your SharePoint site URL ready, you can now connect it as a data source in Power BI Desktop.

1. Open "Get Data"

In Power BI Desktop, go to the Home ribbon and click on Get Data. From the dropdown, select More... This will open the main data source window.

2. Search for the SharePoint Folder Connector

In the Get Data window, use the search bar to type "SharePoint". You'll see several options. The one you want is SharePoint folder. Be careful not to select "SharePoint Online list" or "SharePoint list," as those serve different purposes. Select SharePoint folder and click Connect.

3. Enter Your Site URL

A dialog box will appear asking for the Site URL. This is where you paste the root URL you copied earlier. Don't use the full, long URL that points directly to a document library or subfolder.

Before clicking OK, you have an option for the "Data connectivity mode," typically showing "2.0 (Beta)" and "1.0 (Deprecated)." For most newer SharePoint Online sites, the "2.0" option is faster and more reliable. Stick with that unless you run into issues.

Click OK.

4. Authenticate Your Account

If this is your first time connecting to this SharePoint site, Power BI will prompt you to sign in. Select Microsoft account and click Sign in. Use the same login credentials you use to access SharePoint - typically your standard corporate email and password. Once authenticated, click Connect.

After a moment, Power BI will show you a Navigator window that lists every single file found anywhere on that SharePoint site. It might look a little overwhelming, but don't worry - we'll clean this up in Power Query next.

Click the Transform Data button instead of Load. This is critical because you almost always need to apply some transformations before loading the data into your model.

Combine and Transform Files in Power Query

Clicking "Transform Data" opens the Power Query Editor, which is where the real work of combining your files happens. This is your workshop for cleaning, shaping, and merging data before it even hits your report.

1. Filter to Your Target Folder and Files

Your screen will show a table with metadata for every file in the SharePoint site, including columns like Content, Name, Folder Path, etc.

First, use the filter on the Folder Path column to select only the folder containing the files you want to combine. This weeds out everything else.

Next, it's wise to filter the list to only include the file types you care about. For example, if you just want to combine Excel files, use the text filter on the Name or Extension column to select rows where the extension is ".xlsx" or a similar one. You should also filter out any temporary files that might appear (these often start with a tilde ~).

After filtering, a single column is now crucial: the Content column. Right now, it just says "Binary" for each row. The actual data from your files lives inside these binary files.

2. Use the "Combine Files" Feature

To combine the files, locate the combined-arrow-down icon in the header of the Content column. Click it.

This triggers Power BI's built-in file combining process. A new window will appear, asking you to show Power BI how to handle the files.

1. Select a Sample File: Power BI needs to know the structure of your files, so it uses one of them as a template. You can typically just leave this as "First File".
2. Select the Data Object: If your Excel files have multiple sheets or tables, you'll need to select the one that contains the data you want to import. In most cases, you'll select a sheet like "Sheet1".

A preview of the data will appear. If it looks correct, click OK.

3. Review the Combined Data

Power BI now works its magic. It will process for a few seconds and then present you with a single, unified table containing the data from all the files in your folder. You’ll notice two important things:

• Power Query has applied several transformation steps on its own to make this happen. You can see these in the "Applied Steps" pane on the right.
• A new column was added at the beginning called Source.Name. This column contains the filename from which each row of data originated. This is incredibly useful! For instance, if your files are named "Sales_Jan.xlsx," "Sales_Feb.xlsx," etc., you can use this column to extract the month for creating visualizations over time. You should always rename it (e.g., Month, or Sales Person, etc.), or use Extract functions in Power Query to use that data in reports.

From here, you can perform any additional data cleaning you need - things like changing data types, removing unnecessary columns or promoting headers. Once you're happy with the result, click Close & Apply on the Home ribbon.

Tips & Troubleshooting Common Issues

Keep Your File Structure Consistent

For the combine feature to work without errors, all files in the SharePoint folder must have the same structure. That means the columns need to be in the same order and have the same names. If "Sales_Jan.xlsx" has a column named "Revenue" but "Sales_Feb.xlsx" calls it "Total Sales," the combine operation will stumble.

"Access to the resource is forbidden" Error

This old classic is almost always because the wrong URL was used. Go back to your data source settings (File > Options and settings > Data source settings). Find your SharePoint connection, click Edit Source, and ensure you're using the root site URL, not the full folder or document library path.

Refreshing Doesn't Work After Publishing to Power BI Service

If you've published your report to the Power BI Service and your scheduled refreshes are failing, you may need to update your credentials. Navigate to the dataset's settings in the Power BI Service, find the "Data source credentials" section, and click Edit credentials. Sign in again using OAuth2 with your organizational account to re-authenticate the connection.

Handling "Error in the 'Transform File' query"

This message often indicates a data mismatch. For instance, a new file was added to the folder that has an extra column, a different sheet name, or a number appearing in a column Power BI expects to be text. To troubleshoot this, go back into the Power Query Editor, check the applied steps, particularly the "helper queries" that Power BI generated during the combine process. Look at the data types and column names to ensure everything lines up with your latest files.

Final Thoughts

You now have a powerful, automated system for getting data from multiple files directly into Power BI. By connecting to a SharePoint folder, you've removed the tedious and error-prone step of manually combining data, allowing you to focus on finding insights rather than just preparing data. Any time a new file is added to that folder, a simple data refresh is all you need to keep your reports perfectly up-to-date.

Creating and maintaining efficient data pipelines is essential, but it shouldn't take an entire day of clicking through menus and troubleshooting connections. At Graphed , we’ve made connecting to your data incredibly simple by taking care of the complicated parts for you. Just connect sources like Google Analytics, SharePoint, or Salesforce in a few clicks, and then build real-time dashboards simply by describing what you want in plain English. Your time is better spent discovering insights, not wrangling data sources.