How to Connect Power BI to an Excel File on OneDrive or SharePoint

Introduction

Trying to connect Power BI to an Excel file stored in OneDrive or SharePoint can cause immense frustration if you don’t know how. The problem often boils down to getting the right URL—just grabbing the link from the browser or using the “Copy Link” unfortunately doesn’t work (if only life were that simple). But don’t worry – I’ll walk you through the exact steps, and by the end of this guide, you’ll finally feel like you can put down the hammer.

How To

Step 1: Open the File in Excel Desktop

Start by opening the Excel file you have stored in Onedrive or Sharepoint that you want to connect to in the desktop version of Excel (this is important—don’t use the online version for this step).

1. Once the file is open, click on the File tab in the top left corner.
2. From the menu, select Info on the left-hand side. This will bring up some details about the file.

In the Info section, you’ll see a button labelled Copy Path. It’s typically located at the top left, near the file name. Click this button to copy the file’s full URL to your clipboard.

Step 2: Clean Up the URL

Once you’ve copied the file path, paste it into a text editor (like Notepad or Word) so you can edit it. The path you’ve copied will look something like this:

https://companyname.sharepoint.com/sites/sitename/Shared%20Documents/filename.xlsx?web=1

Notice the part at the end, ?web=1? That’s not helpful for Power BI—it’s meant for a browser preview. Delete ?web=1 so the URL is clean and points directly to the file. After editing, your URL should look like this:

https://companyname.sharepoint.com/sites/sitename/Shared%20Documents/filename.xlsx

Now you’ve got the correct link.

Step 3: Connect to the File in Power BI

Now that you have the proper URL, it’s time to switch over to Power BI.

1. 1. Open Power BI Desktop and head to the Home tab.

1. 1. Click on Get Data (the big button in the top left).

1. 1. From the list of available connectors, select Web. Don’t worry—this works for OneDrive and SharePoint files.

1. 1. A pop-up box will appear asking for the URL. Paste in your cleaned-up file path and click OK.

Step 4: Authenticate the Connection

At this point, Power BI will ask you to log in to access the file. This is because files stored on OneDrive or SharePoint are protected by your Microsoft 365 credentials.

1. 1. When prompted, choose Organisational Account as the authentication method.

1. 1. Log in using the same credentials you use for OneDrive or SharePoint.

1. 1. If the login is successful, Power BI will connect to the file and retrieve its contents.

Step 5: Load or Transform the Data

Once connected, Power BI will display a preview of the tables, sheets, or named ranges in your Excel file.

• • Select the Data: Tick the checkboxes for the data you want to use.

• • Transform or Load: If your data needs a bit of cleaning or restructuring, click Transform Data to open Power Query. Here, you can rename columns, filter rows, or merge datasets. If your data is ready to go, just click Load to bring it straight into Power BI.

Now you’re ready to start building your visuals and dashboards!

Why This Works

You might wonder why we have to go through all this instead of just grabbing a link from OneDrive or SharePoint. The reason being, links you get from the “Copy Link” button are designed for sharing, not direct access. They often include parameters like ?web=1 or even tokens that confuse Power BI. By using the Copy Path button in Excel Desktop, you get the file URL Power BI needs (albeit with a small adjustment required as outlined above).

Pro Tips for a Smooth Experience

1. Keep the File Path Consistent: Once you’ve connected Power BI to your file, avoid moving or renaming it in OneDrive or SharePoint. Doing so will break the connection, and you’ll have to reconnect.
2. Scheduled Refresh: If you’re publishing your report to Power BI Service, remember to set up your credentials in the dataset settings to enable scheduled refreshes. This way, your data stays up-to-date without manual effort.
3. Verify Permissions: Make sure your account has the necessary permissions to access the file. If someone else shared the file with you, ensure you’ve been granted edit or view permissions.
4. Use Named Ranges: If your Excel file has multiple sheets or messy data, consider using named ranges. These make it easier to connect directly to the specific data you need.

And That’s It!

Hopefully you’re now able to connect to the spreadsheet that’s been causing you frustration stored on OneDrive or SharePoint. With this setup, your reports can stay connected to live data that is centralised, making updating the report easier for anyone that has the required access. Hope this helps!