How to Import SharePoint File to Power BI

Tired of manually downloading files from SharePoint just to update your Power BI reports? You can connect Power BI directly to your SharePoint files, creating a dynamic link that turns your reports into self-updating sources of truth. This article will guide you through connecting both individual files and entire folders from SharePoint to Power BI, as well as how to connect to SharePoint Lists.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Connect SharePoint to Power BI?

Connecting Power BI directly to data stored in SharePoint is more than just a convenience, it is a fundamental best practice for creating robust, collaborative business intelligence solutions. When you store your source files (like Excel workbooks, CSVs, etc.) in a SharePoint document library and connect Power BI to them, you unlock several key advantages:

• Single Source of Truth: Everyone on your team is working from the same files. There are no more questions about whose "Final_Report_v2_final_final.xlsx" is the correct one. The file in SharePoint is the definitive version.
• Automatic Refreshes: Once connected, you can schedule your Power BI report to refresh automatically. When a team member updates the source file in SharePoint, the changes will flow through to your Power BI dashboard on the next refresh cycle, without any manual intervention.
• Enhanced Collaboration: SharePoint is built for teamwork. Multiple users can contribute to and update source data files, and Power BI handles the rest. This creates a seamless workflow from data entry to data visualization.
• Version History and Security: SharePoint's built-in version control and permission settings add a layer of governance to your data. You can see who changed what and when, and you can control who has access to view or edit the source files.

By leveraging SharePoint as a data repository, you move away from static, disconnected reports and toward a more integrated and automated analytics environment.

Prerequisites: What You’ll Need

Before you start pulling data, make sure you have a few things set up to ensure a smooth process. You don't want to get halfway through and hit a roadblock due to a permission issue.

• Power BI Desktop: You'll need the free Power BI Desktop application installed on your computer. This is where you will build the connection and design your report.
• Access to a SharePoint Online site: You need to be a member of the SharePoint site where the files or lists are located.
• File Permissions: You must have at least "Read" permissions for the file, folder, or list you want to connect to. Without this, Power BI won't be able to access the data.
• SharePoint Site URL: You will need the root URL of your SharePoint site. This is a common point of confusion. You don't need the full path to the file, just the main site address.

For example, if your file is located at https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Reports/Sales_Q3.xlsx, your root site URL is just https://yourcompany.sharepoint.com/sites/Marketing.

Connecting a Single File from a SharePoint Folder

Let's start with the most common scenario: connecting to a single Excel or CSV file stored within a SharePoint document library. While it seems like you should use the "Excel" or "Web" connector, the most reliable method is to use the "SharePoint Folder" connector, even for a single file. This method is more robust and handles organizational authentication much better.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 1: Get Data in Power BI

Open Power BI Desktop and on the Home ribbon, click on Get Data. From the dropdown menu, select More... to open the full list of data connectors.

Step 2: Choose the SharePoint Folder Connector

In the Get Data window, type "SharePoint" into the search bar. Select SharePoint folder from the list and click Connect.

Step 3: Enter Your SharePoint Site URL

This is the crucial step. In the dialog box that appears, paste the root URL of your SharePoint site - not the full path to the document library or the file itself. Click OK.

Power BI will then ask you to sign in. Be sure to use your organization's Microsoft account credentials.

Step 4: Navigate and Filter Files in Power Query

After connecting, Power BI will show you a list of every single file from that SharePoint site. This can be overwhelming. Click Transform Data to open the Power Query Editor, which is where you'll clean and filter this list.

You’ll see several columns, such as File Name, Folder Path, etc. Use the filter controls on these columns to locate your specific file. The Folder Path column is especially useful for narrowing down the list to a specific document library or subfolder.

Step 5: Combine the File Content

Once you've filtered the list to show only the file you want, find the column named Content. It will have "Binary" listed for your file. To the right of the word "Binary", click the double downward-facing arrows icon. This is the Combine Files button.

Power BI will now preview the file. If it's an Excel file with multiple sheets, you'll be asked to select the sheet you want to import data from. Make your selection and click OK.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 6: Finalize in Power Query

Behind the scenes, Power BI automatically creates a series of transformation steps and helper queries to extract the data from that binary file. You'll land back in the Power Query Editor with your data loaded and ready for any additional transformations like changing data types, removing columns, or creating custom calculations.

When you're happy with how the data looks, click Close & Apply on the Home ribbon to load it into your Power BI data model.

Combining Multiple Files in a SharePoint Folder

The real power of the SharePoint Folder connector shines when you need to combine multiple files that share the same layout - for example, monthly sales reports, weekly activity logs, or daily exports from another system.

The process is nearly identical to connecting a single file, with one key difference in mindset.

1. Follow Steps 1-3 from the previous section to connect to your SharePoint site using the SharePoint Folder connector.
2. In the Power Query Editor, filter the folder path to the location where all your similar files are stored. For example, if you have a folder named "Monthly Sales," filter the view to show only the files within that folder.
3. Again, find the Content column and click the Combine Files icon.

This time, Power BI will use the first file as a "sample file" to determine the steps needed to import and transform the data. It will then apply those exact same steps to every other file in the folder and append the results into one large table. This is incredibly powerful and automates what would otherwise be a tedious copy-and-paste task.

Note: For this process to work properly, all files need to have a consistent structure. This means the same column names, in the same order, and on the same sheet name (if using Excel files). Any deviation can cause the refresh to fail.

Connecting to a SharePoint List

SharePoint Lists are another common data source for Power BI dashboards. Unlike document libraries that store files, Lists store data in a structured, table-like format directly within SharePoint. The connection process is more direct.

Step 1: Choose the SharePoint Online List Connector

In Power BI Desktop, go to Get Data > More... In the Get Data window, search for and select SharePoint Online List, then click Connect.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Enter the Site URL

Once again, enter your root SharePoint Site URL. You’ll be presented with some advanced options. It's best to set the Implementation view to 2.0, as it’s a newer, more efficient API. The view mode can be left as "All Items". Click OK.

Step 3: Select Your List

The Navigator window will appear, showing all the available lists on that SharePoint site. Select the list you want to import and you’ll see a preview of the data. Click Transform Data to open it in Power Query.

Step 4: Clean Up the Data in Power Query

SharePoint Lists often come into Power BI with many extra columns that aren't useful for analysis (e.g., internal ID columns, ContentType, various system-related fields). In the Power Query editor, it's a good practice to go through and remove any columns you don’t need. This will make your data model cleaner and improve performance. Then click Close & Apply.

Best Practices and Common Issues

Connecting SharePoint and Power BI is powerful, but a few common tripping points can cause issues. Here’s how to avoid them.

• Use the Root URL: Remember, always use the main site URL (e.g., https://yourcompany.sharepoint.com/sites/Marketing) in the Site URL prompt, not the full path to a specific file or folder. Pasting the full URL is the number one cause of connection errors.
• Verify Your Permissions: If you get an access denied or credentials error, double-check that you have at least "Read" access to the SharePoint site and the specific file or list.
• "Combine Files" Errors: If Power BI reports an error when combining files, it's almost always because one or more files in the folder have a different structure (e.g., a changed column name, a different sheet name in an Excel file). Isolate the problematic file and fix its structure to match the sample file.
• Filter Before Expanding: When using the SharePoint Folder connector, always filter the list of files down to what you need before clicking "Combine Files." This drastically improves performance, as Power BI won’t waste time evaluating files it doesn’t need.

Final Thoughts

By connecting Power BI with SharePoint, you can transform your manual reporting workflows into automated, collaborative analytics systems. Whether you're combining monthly Excel exports or pulling data from a SharePoint List, these methods provide a reliable foundation for your dashboards, ensuring your data is always current and centrally managed.

While Power BI excels with internal sources like SharePoint, pulling together data from multiple marketing and sales platforms can still be a headache. To fix this, we created Graphed to act as your AI data analyst. You can connect sources like Google Analytics, Shopify, Facebook Ads, and Salesforce in seconds, then simply describe the dashboard you want in plain English. Graphed automates the manual work, turning hours of data wrangling into a 30-second conversation and letting you get back to making decisions.