How Do I Connect Power BI to SharePoint Folder?

Connecting Power BI to a SharePoint folder is a powerful way to automate your reporting, especially when you need to combine data from multiple files. Instead of manually downloading and merging weekly Excel reports or monthly CSV exports, you can point Power BI to a single folder and let it handle the heavy lifting. This article will walk you through the entire process, from getting the right folder path to combining your files into a single, clean dataset ready for analysis.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Connect Power BI to a SharePoint Folder?

The primary reason to use this method is to consolidate multiple, identically structured files into one master table. Imagine you have a SharePoint folder where your team saves monthly sales reports. Each report is a separate Excel file (Jan-Sales.xlsx, Feb-Sales.xlsx, etc.), but they all have the same columns.

Instead of opening each file, copying the data, and pasting it into one giant spreadsheet, you can connect Power BI directly to the folder. Power BI will then:

• Identify all the files within that folder.
• Open each file and extract the data.
• Stack, or append, the data from all files into a single table.
• Allow you to refresh the entire dataset with one click whenever a new file is added.

This approach saves an enormous amount of time, eliminates copy-paste errors, and ensures your dashboard is always up-to-date. It's perfect for consolidating recurring reports like monthly social media analytics exports, weekly campaign performance data, or daily logs.

Before You Begin: What You'll Need

To make this process as smooth as possible, make sure you have a few things squared away first. This setup will save you from common headaches later on.

1. Power BI Desktop

This tutorial is for Power BI Desktop, the free authoring tool from Microsoft where you build your reports. If you don't have it installed, you can download it for free from the Microsoft Store.

2. Access to the SharePoint Site

You need to have permission to access the SharePoint site and the specific folder containing your files. This means you’ll use the same Microsoft account to log into SharePoint that you'll use to authenticate in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Consistently Structured Files

This is the most critical requirement. For Power BI's "Combine Files" feature to work correctly, all the files you want to combine must have the same structure. For example, if you are combining Excel files:

• The column headers must be identical in all files. Campaign Name is different from Campaign.
• The columns should be in the same order.
• The data should be in the same location (e.g., the same sheet name, like "Sheet1," in every workbook).

If one file deviates from this structure, the combine process will likely throw an error during refresh. It's best to establish a clean, consistent template for these files from the beginning.

Step-by-Step Guide: Connecting Power BI to a SharePoint Folder

Once your prerequisites are in order, you're ready to make the connection. Follow these steps carefully.

Step 1: Get the Correct SharePoint Site URL

This is the step where most people get tripped up. You don't need the long URL to the specific document library or sub-folder. You only need the URL for the root SharePoint site.

Here’s how to find it:

1. Navigate to your SharePoint site's home page in your web browser.
2. Look at the URL in the address bar.
3. Copy the first part of the URL, up to the site name.

For example, if the full browser URL to your folder is:

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

You only need this part:

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

This is the root URL for the "MarketingTeam" site. Copy this URL and keep it handy.

Step 2: Get Data in Power BI Desktop

Open a new Power BI Desktop file. In the Home ribbon at the top, click on Get Data. To see all the options, click More... at the bottom of the dropdown menu.

Step 3: Choose the 'SharePoint Folder' Connector

The "Get Data" window will pop up with a list of all available data connectors. In the search bar on the left, type "SharePoint" to filter the list. Select SharePoint folder and click Connect.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Enter the Site URL and Connect

A new dialog box will ask for the Site URL. Paste the root site URL that you copied in Step 1. Click OK.

Power BI will now attempt to connect to your SharePoint site. If you are not already logged in, you will be prompted to sign in. Choose "Microsoft account," click "Sign in," and enter the credentials you use for SharePoint.

Step 5: The Initial File View

After a moment, a Navigator window will appear, showing you a preview of every single file found on that entire SharePoint site. Don't worry! We'll filter this down. You will see columns of metadata about each file, such as Name, Folder Path, and Date created.

Find the table of files and click the Transform Data button at the bottom right. This will open the Power Query Editor, which is where we will filter, combine, and clean the data.

Combining Files in the Power Query Editor

You're now in the Power Query Editor. This is Power BI's data transformation engine, and it’s where all the magic happens. On your screen, you should see the list of all files from your SharePoint site.

Step 1: Filter to Your Target Folder and File Type

Before combining files, you need to tell Power Query which files you actually want. You can use the filter buttons on the column headers just like in Excel.

1. Filter by Folder Path: Find the Folder Path column. Click the filter arrow, go to Text Filters, and choose "Contains." Enter a unique part of your target folder's path to isolate just that folder. For example, if your files are in /Monthly Reports/, you can use that. This ensures you aren't pulling files from other libraries.
2. Filter by File Type (Optional but Recommended): Find the Extension column and filter it to only show the type of file you want to combine (e.g., .xlsx for Excel files or .csv for CSV files). This prevents you from accidentally trying to combine a Word document or a temporary file.

Step 2: Combine the Files

Now that you've filtered the list to only show the files you need, you're ready to combine them.

Find the Content column. The header of this column should have an icon with two downward arrows. This is the Combine Files button. Click it.

Power BI will now pop up another dialog box. It's asking for a "sample file" to use as a "template." Based on one of your files from the list, it will determine how to process all the others. For example, if you are combining Excel files, you'll need to tell it which sheet to get the data from ("Sheet1," for example).

Select your sample object (like a specific sheet or table) and click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Review the Final Result

Power Query will work for a few seconds. In the background, it creates a set of helper functions and parameters that tell Power BI how to open each file, extract the specified data, and stack it all together. When it's done, your view will change from a list of files to a single, combined table of data.

You'll notice a new first column has been added automatically: Source.Name. This column contains the filename from which each row originated. This can be very useful for diagnostics or if you want to analyze data based on its source file (e.g., to create a date from a filename like Jan_2024_Sales.xlsx).

Step 4: Clean Up and Close & Apply

Your data is now combined! Before leaving Power Query, it's a good practice to perform some basic data cleaning:

• Check Data Types: Make sure your date columns are set to the "Date" type, numerical columns are "Decimal Number" or "Whole Number," and text columns are "Text." Power Query is pretty good at guessing, but it's always worth double-checking.
• Remove Extra Columns: If you don't need the Source.Name column, feel free to right-click the header and select "Remove."
• Review Column Headers: Ensure the column headers came through correctly.

Once you are happy with your combined table, go to the Home tab in the Power Query Editor Ribbon and click Close & Apply. The query will run and load your final, combined data table into your Power BI data model, ready for you to build visuals.

Tips for Success and Common Pitfalls

Here are a few things to keep in mind to avoid common issues:

• Consistency Is King: The most common source of refresh errors is inconsistent file structures. If someone adds a file where a column name is slightly different ("Date" vs "date") or a new column is added, the combine process can fail. Using a locked Excel template for data entry can prevent this.
• Bookmark That Site URL: You’ll probably use that SharePoint site root URL again. Save it somewhere handy. It's the key to getting started.
• Be Patient with Authentication: SharePoint and Power BI authentication can sometimes be tricky. If you get an access error, try signing out and signing back into your Microsoft account in Power BI Desktop, or clearing your permissions under Data source settings.
• Test with a Few Files First: If you have thousands of files, create a test sub-folder with just two or three files to set up and test your query. Once you confirm the logic works, you can change the folder path to the full directory.

Final Thoughts

Connecting Power BI to a SharePoint folder is a game-changer for automating repetitive reporting tasks. By setting it up once, you create a robust pipeline that combines multiple files into a single source of truth, allowing you to simply hit "Refresh" to get the latest data instead of spending hours on manual prep work.

While this process is a huge leap forward from manual data wrangling in spreadsheets, building and managing dashboards in complex tools like Power BI can still be a heavy lift for many teams. We built Graphed because we believe getting insights from your data shouldn’t require a steep learning curve. By connecting your sources like Google Analytics, Shopify, or HubSpot, you can use plain English to build real-time dashboards and reports in seconds, letting you and your team focus on making decisions, not on wrestling with software.