How to Connect Power BI to SharePoint Excel File

Tired of manually downloading an Excel file from SharePoint every week just to update your Power BI report? You can directly connect your report to that same file, creating an automated dashboard that refreshes itself. This guide breaks down exactly how to link Power BI to an Excel file stored in SharePoint, step by step.

Why Connect Power BI and SharePoint Excel Files?

Before jumping into the "how," let's quickly cover the "why." Connecting your report directly to the source file in SharePoint turns your reporting process from a manual chore into an automated system. Here are the biggest benefits:

• Automation is a time-saver. Once you set up the connection, you can schedule a refresh. Power BI will automatically pull the latest data from the Excel file on a daily or even hourly basis. No more "download, open, refresh, publish."
• It creates a single source of truth. When everyone on your team updates the same central Excel file on SharePoint, anyone viewing the Power BI report is guaranteed to be looking at the most current data. This eliminates version control chaos with multiple copies of the same file.
• Your data is secure and centralized. SharePoint provides robust security and permission controls. By keeping your source file there, you leverage your organization's existing security framework instead of having sensitive data saved on various local machines.
• Collaboration gets easier. Team members can update the raw data in the Excel file on SharePoint without needing Power BI licenses or an understanding of how the report is built. The data and the visualization are separate but connected, letting each person focus on their part of the process.

The Crucial First Step: Nailing the Correct File Path

This is where over 90% of people get stuck. If your connection fails, it's almost always because you're using the wrong link. Simply copying the URL from your browser's address bar won't work. You need to get the direct file path, and here is how to do it correctly.

There are two reliable ways to grab the correct path.

Method 1: Using the "Details" Pane in SharePoint

1. Navigate to the SharePoint Document Library where your Excel file is stored.
2. Find your file in the list. Don't click to open it in the browser. Instead, click the three vertical dots (•••) next to the filename.
3. From the context menu, select Details. A pane will slide out from the right side of the screen.
4. Scroll down in the details pane until you find the "Path" section.
5. Click the Copy path icon next to the file path. This will copy the direct link you need.

Method 2: Using the Excel Desktop App

This method can be even more reliable as it gives you the cleanest link from the start.

1. From SharePoint, click on your Excel file to open it in an Excel for the Web browser tab first.
2. Once it's open, look for an "Editing" button near the top. Click it and select "Open in Desktop App."
3. Excel will launch on your computer and open the file.
4. Once it is open, go to the File menu in Excel.
5. Select the Info tab from the menu on the left.
6. At the very top, directly under the filename, you will see the full file path. Click where it says Copy Path.

Cleaning the URL

Whichever method you use, take a look at the URL you copied. You need to delete everything starting from the question mark ? at the end of the link. For example, if your copied link looks like this:

https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Campaign%20Data.xlsx?web=1

You need to remove the ?web=1 part, so your final URL is:

https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Campaign%20Data.xlsx

Connecting Power BI to the SharePoint Excel File (Step-by-Step)

Now that you have the clean, correct file path, you're ready to connect it inside Power BI Desktop.

1. Get Data from Web

Open your Power BI Desktop file. In the Home ribbon, click on the Get Data dropdown and select Web from the list of common connectors.

2. Enter the File Path

A dialog box titled "From Web" will appear. Simply paste the cleaned file path you copied from SharePoint into the URL field and click OK.

3. Authenticate Your Account

Power BI now needs permission to access your SharePoint site. You will be prompted to sign in.

• On the left side of the "Access Web content" window, select Organizational account. Do not use Anonymous or the other options.
• Click the Sign in button and enter your Microsoft 365 (work or school) credentials - the same ones you use to log into SharePoint.
• After signing in successfully, click Connect.

4. Select Your Data in the Navigator

After a moment, the Power BI Navigator window will appear. This window shows you all the available data within your Excel file. You will see a list of any sheets, as well as any named Tables.

Pro Tip: It is highly recommended to format your data in Excel as a "Table" (select your data and press Ctrl+T) before connecting it to Power BI. Tables have defined column headers and dynamically adjust their size as you add or remove rows. Connecting to a Table is much more robust than connecting to a whole worksheet, which can pull in empty rows or break if the data layout changes.

Check the box next to the table or sheet you want to import. A preview will appear on the right.

5. Load or Transform Your Data

At the bottom of the Navigator window, you have two options:

• Load: This option will immediately load the data into your Power BI model as-is. This is fine for clean, simple data.
• Transform Data: This is the more powerful option. It opens the Power Query Editor, where you can clean and shape your data before loading it. You can remove columns, change data types, filter rows, and perform hundreds of other transformations. It’s almost always a good idea to at least take a quick look in Power Query before loading.

Once you've made your choice, your data from the SharePoint Excel file will be imported into Power BI. You can now build visuals with it just like any other data source.

Setting Up Automatic Refresh in Power BI Service

The real magic happens when you publish your report and configure an automatic refresh, so you never have to manually update it again.

1. Publish Your Report

In Power BI Desktop, save your report and then click the Publish button in the Home ribbon. Choose a workspace in the Power BI Service to publish it to.

2. Configure Dataset Settings

Go to the Power BI Service in your web browser (app.powerbi.com) and navigate to the workspace where you published your report. Find the new dataset (it will have the same name as your report) and click the three dots (•••) next to it, then select Settings.

3. Edit Data Source Credentials

In the "Settings" page, expand the Data source credentials section. You’ll see a warning that the credentials are not valid. Click Edit credentials.

A new dialog box will appear. Change the "Authentication method" to OAuth2 and the "Privacy level setting" to Organizational. Click Sign in and log in with your Microsoft 365 credentials one last time. This grants the Power BI Service ongoing permission to access the SharePoint file in the background.

4. Schedule the Refresh

Now scroll down and expand the Scheduled refresh section. Toggle the switch to On. You can now set your desired refresh frequency (Daily or Weekly), select your Time zone, and add specific times for the refresh to run. For most business reports, a single daily refresh in the early morning is perfect.

Click Apply. And that's it! Your Power BI report will now automatically check your SharePoint Excel file for new data and update itself according to the schedule you defined.

Troubleshooting Common Errors

• Error: "Access to the resource is forbidden." This is almost always due to an incorrect URL. Double-check that you copied the direct path (not the browser link) and removed everything after the "?".
• Error: "Credentials could not be authenticated." Try re-entering your credentials in the dataset settings in Power BI Service. Ensure you are using the "Organizational account" login option with your Microsoft 365 credentials.
• Refresh Fails in the Service: Confirm that the Microsoft account you used to set up the credentials still has at least "Read" access to the SharePoint folder and file. If the file has been moved or renamed, the refresh will fail. You'll need to update the source in Power BI Desktop and republish.
• Structure Has Changed: If a column is renamed or deleted in the Excel source file, the Power BI refresh will break because it's looking for a column that no longer exists. Maintain a consistent column structure in your source file to avoid this.

Final Thoughts

By connecting Power BI directly to your Excel file on SharePoint, you create a robust, secure, and fully automated reporting pipeline. The most important part of the process is getting the clean, direct path to the file - once you have that, the rest of the setup is a straightforward process of signing in and scheduling your refresh.

This kind of automation saves countless hours and makes your reporting far more reliable. But we believe getting insights shouldn't require this much setup at all. That’s why we created Graphed. Our platform is designed to eliminate the manual work of connecting sources and building reports. Instead of going through multiple steps in different applications, you just connect your marketing and sales platforms once, then use plain English to ask questions and build the dashboards you need in seconds. No complex BI tools to learn, no cleaning file paths - just instant, real-time insights.