How to Get Excel File from SharePoint to Power BI

Connecting an Excel file from SharePoint to Power BI should be a straightforward task, but it’s a process that trips up many users. The secret lies in using the correct file path - not the URL you see in your browser. This tutorial will walk you through the proper method to establish a stable, refreshable link between your SharePoint-hosted Excel files and your Power BI reports.

The Benefits of Connecting Directly to SharePoint

Before jumping into the "how," let's quickly cover the "why." You might be tempted to just download the Excel file and upload it to Power BI. While that works for a one-time analysis, it creates a manual process that is slow and prone to errors. Connecting directly to the file in SharePoint is a much better approach for a few key reasons:

• Single Source of Truth: Everyone on your team works from the exact same file in a central location. There’s no confusion over which version of "Sales_Data_Final_v3.xlsx" is the right one.
• Automated Refreshes: Once connected, you can schedule your Power BI report to automatically refresh. When a team member updates the Excel file in SharePoint, your Power BI dashboard will reflect those changes without you having to lift a finger.
• Collaboration and Version Control: SharePoint handles version history automatically. You can always see who made changes and when, providing a layer of governance over your data.
• Security: Permissions are managed through your existing Microsoft 365 environment, ensuring only authorized users can access the data.

In short, a direct connection saves you from the weekly headache of downloading, re-uploading, and republishing reports. It turns your Power BI dashboard into a living, automated tool instead of a static, quickly outdated one.

The Wrong Way: Why Copying the Browser URL Fails

The number one mistake people make is trying to use the URL from their browser's address bar. It seems logical, but it won't work for creating a refreshable connection.

When you open an Excel file in SharePoint online, the URL looks something like this:

https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FYourSite...[long string of characters]

This URL tells your browser to open the file within the SharePoint web interface. It’s a link for human interaction, not a direct data pipe for a service like Power BI. If you use this URL in Power BI's "Get Data from Web" connector, you'll likely get an error or connect to the SharePoint page itself, not the raw Excel file data.

To do it right, we need to get the direct file path.

The Right Way: A Step-by-Step Guide to Getting the SharePoint File Path

The correct method is a little counter-intuitive but guarantees a stable connection every time. The trick is to open the file in the Excel desktop application to grab its true location.

Step 1: Navigate to Your Excel File in SharePoint

Start by opening your web browser and navigating to the SharePoint site and document library where your Excel file is stored. Find the workbook you want to connect to Power BI. Don't click on it yet.

Step 2: Open the File in the Excel Desktop App

This is the most important step in the process. You need to open the file in the full desktop version of Excel, not the browser-based version.

Hover over the file name, click the three-dot menu (...), select Open, and then choose Open in app. If you are in the Classic SharePoint view, you might need to click the file to open it in the browser first, then look for a button near the top that says "Open in Desktop App."

Why this step? The desktop app knows the file's direct, true path, which is exactly what Power BI needs to establish a connection.

Step 3: Find and Copy the Correct File Path

With the file now open in your Excel desktop application, go to the menu at the top.

1. Click on File in the top left corner. This will open the blue "Info" backstage view.
2. Near the top, under the file name, you'll see the file's path. Click the Copy path button right beside it.

The path you just copied to your clipboard should look something like this:

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

Step 4: Clean Up the URL

You’re so close! The path you copied is almost perfect. But there’s one small part you need to remove for Power BI to read it correctly: the trailer at the end.

Paste the path you copied into a text editor (like Notepad) or directly into the Power BI dialog box later. Look for the question mark at the end.

• BEFORE: https://yourcompany.sharepoint.com/sites/.../Sales%20Report.xlsx?web=1
• AFTER: https://yourcompany.sharepoint.com/sites/.../Sales%20Report.xlsx

Simply delete the ?web=1 part. This small change tells Power BI you want the raw data from the file itself, not the web view for a browser. Your URL is now ready for Power BI.

Connecting the File in Power BI Desktop

Now, let's switch over to Power BI Desktop and put that clean URL to work.

Step 1: Get Data from Web

Open Power BI Desktop. In the Home ribbon at the top, click on Get Data and select Web from the most common connectors. If you don't see it, choose "More..." and find "Web" in the list.

Step 2: Paste Your Cleaned URL

A dialog box titled "From Web" will appear. Paste the cleaned-up URL you prepared in the previous step into the URL field. Click OK.

Step 3: Authenticate Using Your Microsoft Account

Power BI now needs to prove it has permission to access your SharePoint site. You'll be prompted to sign in.

1. On the left side of the authentication window, select Organizational account.
2. Click the Sign in button and enter the credentials for your Microsoft 365 or company account (the same one you use for SharePoint).
3. Once you are signed in, click the Connect button.

This method, known as OAuth2, is secure and scalable, as it doesn't require you to manage passwords directly within Power BI.

Step 4: Select Your Data in the Navigator

If the connection is successful, the Power BI Navigator window will open. Here, Power BI shows you all the sheets and any officially defined Excel Tables within your workbook.

It’s always a best practice to select a Table (indicated by the blue header icon) rather than a Sheet. Tables are more structured and less likely to break if someone adds notes or new columns outside the table range.

Check the box next to the sheets or tables you want to import. You'll see a preview of the data on the right. From here, you can:

• Load: Click this if your data is clean and ready. It will be loaded directly into your Power BI data model.
• Transform Data: The better option in most cases. This opens the Power Query Editor, where you can clean, shape, and prepare your data before loading it.

Keeping Your Data Fresh: Setting Up Scheduled Refresh

The whole point of this process is automation. To make your report update automatically, you must publish it to the Power BI service and configure a refresh schedule.

1. Publish Your Report: Once you've built your report in Power BI Desktop, click Publish on the Home ribbon and choose a workspace.
2. Access Dataset Settings: Log in to 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 file), click the three-dot menu (...), and select Settings.
3. Edit Data Source Credentials: Expand the "Data source credentials" section. You'll see a warning next to your SharePoint data source. Click Edit credentials. You’ll be prompted to sign in again with your Organizational account using the OAuth2 authentication method. This gives the Power BI service ongoing permission to access your file.
4. Schedule Refresh: Once your credentials are confirmed, expand the "Scheduled refresh" section. Toggle it on, and then select the frequency (e.g., Daily) and time(s) you want the data to be updated. You can add up to eight refreshes per day on a Pro license.

That's it! Your report is now fully automated. Any changes made to the Excel file in SharePoint will be automatically pulled into your Power BI dashboard on the schedule you set.

Final Thoughts

By learning how to connect Power BI to SharePoint using the direct file path, you've unlocked a way to build robust, automated reports that serve as a single source of truth for your team. You've replaced a manual, error-prone weekly chore with a reliable, hands-off reporting system.

While mastering Power BI is a valuable skill, a lot of data analytics still feels like working around friction - like learning the roundabout trick to get a simple URL. At our core, we believe data shouldn’t be that hard. We built Graphed because we wanted to eliminate these headaches. Instead of jumping through hoops to connect sources one by one, you just connect your platforms like Google Analytics, Shopify, or HubSpot once, and then ask for what you need in plain English - no more hunting for esoteric URLs. Your dashboards are generated and updated automatically, letting you focus on insights, not setup.