How to Connect Power BI to Excel File on OneDrive

Dragging an Excel file into Power BI is simple, but what happens next week when you update the spreadsheet? If you're manually re-uploading the file and refreshing your reports, you're creating unnecessary work. This tutorial will show you how to connect Power BI directly to an Excel file stored in OneDrive, creating an automated, refreshable data pipeline that saves you time and ensures your reports are always up to date.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Connect Power BI to a OneDrive Excel File?

Connecting your Power BI report to a cloud-based Excel file instead of a local copy on your hard drive unlocks several huge advantages. It’s a foundational step in moving from static, manual reporting to a more dynamic and automated workflow.

• Automated Data Refreshes: This is the biggest win. Once connected, you can schedule your Power BI report to automatically refresh its data from the OneDrive file. Change a number in the spreadsheet, and your Power BI dashboard will reflect that change after the next scheduled refresh, with zero manual intervention from you.
• True Collaboration: When your Excel file lives in OneDrive, your whole team can contribute to it. A sales rep can update their numbers, a marketing team member can add campaign spending, and the Power BI report pulls all that collaborative work into one centralized dashboard.
• Single Source of Truth: Say goodbye to version control nightmares like "Sales_Report_V3_FINAL_JAs_Edits.xlsx." The Excel file in OneDrive becomes the single, authoritative source. Everyone works from the same data, and your report is always reflecting the latest reality.
• Accessibility and Security: Your report is no longer tethered to the computer where the local Excel file is saved. You can access and refresh it from anywhere. Plus, you can manage access using your organization's existing Microsoft 365 permissions.

Before You Begin: What You'll Need

To make this process as smooth as possible, make sure you have the following ready to go:

• Power BI Desktop: This is the free application from Microsoft where you'll build your report and set up the initial connection.
• A OneDrive for Business Account: This is a key distinction. The method we're using is designed for OneDrive for Business, which is typically part of Microsoft 365 work or school subscriptions. While it's possible to connect to a personal OneDrive account, it's a bit more complex. If you have your file in a SharePoint folder, the process is nearly identical.
• An Excel File: The spreadsheet you want to use as your data source, saved in your OneDrive for Business. For best results, format your data in Excel as a named table (Insert > Table). Power BI works much better with structured tables than with loose collections of cells.
• A Power BI Service Account: You’ll need to sign into Power BI Service (app.powerbi.com) to publish your report and manage the refresh schedule.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step: Connecting Power BI to Your OneDrive Excel File

The trick to connecting to a OneDrive file isn't using the standard 'Excel Workbook' connector. Instead, we'll use the 'Web' connector. This tells Power BI to access the file via its cloud location, which is what enables automated refreshes.

Step 1: Get the Correct Path from Excel on the Web

First, you need to get a specific URL for your file. Simply copying the URL from your browser's address bar won't work.

1. Navigate to your OneDrive or SharePoint folder in your web browser and open the Excel file. It should open in Excel for the web.
2. Once the file is open, go to File > Info.
3. You'll see a card of information about the workbook. At the bottom, click the Copy path button. This copies the direct link to your clipboard.

Now, open a text editor like Notepad and paste the link. It will look something like this:

https://yourcompany-my.sharepoint.com/personal/your_name/Documents/Reports/MonthlySales.xlsx?web=1

<em>Critical step:</em> You must delete the ?web=1 from the very end of the URL. This small bit of text tells the browser to open the file in the web editor, but it will confuse Power BI. Your final, clean URL should look like this:

https://yourcompany-my.sharepoint.com/personal/your_name/Documents/Reports/MonthlySales.xlsx

Step 2: Connect via the 'Web' Connector in Power BI Desktop

Now, head over to Power BI Desktop to establish the connection.

1. Open Power BI Desktop. From the Home ribbon, click Get Data and select Web from the list of common connectors.
2. A new window will pop up. Paste your cleaned-up URL (the one without ?web=1) into the field and click OK.
3. Power BI will then ask you how to authenticate. Because you're accessing a secure file within your organization, you need to sign in. In the left-hand pane, select Organizational account.
4. Click the Sign in button and log in with your Microsoft 365 (work or school) credentials - the same ones you use for OneDrive.
5. Once you're signed in, click Connect.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Load Your Data in Power Query

After a moment, the Power BI Navigator window will appear, showing you the contents of your Excel file.

1. You will see a list of available tables and sheets within your workbook. This is why using named tables in Excel is a best practice - they'll show up with clear names (like SalesData_2024) instead of generic sheet names (like Sheet1).
2. Select the checkbox next to the tables or sheets you want to import into your model. A preview of the data will appear on the right.
3. Click Transform Data. This is generally better than clicking 'Load' right away, as it opens the Power Query Editor, where you can clean or shape your data before it even hits your report (e.g., change data types, remove unnecessary columns).
4. Once you're happy with your data in the Power Query Editor, click Close & Apply from the Home tab.

That's it! Your Power BI report is now directly connected to your Excel file in OneDrive. You can start building visuals, measures, and calculated columns just as you would with any other data source.

Publishing Your Report and Scheduling a Refresh

The final piece of the puzzle is to automate the updates. This is done in the Power BI Service, not Power BI Desktop.

Publishing to Power BI Service

Within Power BI Desktop, click the Publish button on the Home ribbon. Select the workspace where you want the report to live and let the publishing process complete.

Configuring Your Dataset Refresh

1. Open your web browser and navigate to app.powerbi.com.
2. Find the workspace where you just published your report. Inside, you'll see two items with the same name: the Report and the Dataset. The dataset is what holds the data and the connection info.
3. Hover over the dataset, click the three dots (...), and choose Settings.
4. Expand the Data source credentials section. You’ll see your OneDrive connection. Click Edit credentials. Make sure the 'Authentication method' is set to OAuth2 and sign in again with your Organizational account. This gives the Power BI cloud service permission to access your OneDrive file on your behalf.
5. Next, expand the Scheduled refresh section. Toggle the switch to On.
6. Choose a refresh frequency (e.g., Daily, Weekly), select your time zone, and add one or more times you want the refresh to run.
7. Click Apply.

Your report will now automatically check the OneDrive Excel file for updates at the times you specified, keeping your dashboard data fresh without you having to lift a finger.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Problems and How to Fix Them

If you run into trouble, it's usually one of a few common issues:

• Authentication Fails: Double-check that you're using the 'Organizational account' option when signing in, not 'Anonymous'. This needs to be done both in Power BI Desktop and again in the Power BI Service dataset settings.
• "Access to resource is forbidden" Error: This almost always means you forgot to remove the ?web=1 from the end of the URL you pasted into the Web connector. Go back, clean up the URL, and try again.
• You Used a Personal OneDrive Account: This method is most reliable for OneDrive for Business. Personal accounts can have authentication methods and URL structures that are different and less straightforward for Power BI to handle.
• Refresh Fails with Credential Errors: If an automatic refresh fails, the first place to check is the dataset settings in Power BI Service. Your cached login credentials may have expired. Simply go back to "Data source credentials," edit them, and sign in again to re-authorize the connection.

Final Thoughts

Connecting Power BI to an Excel file on OneDrive fundamentally changes your reporting process. It elevates your spreadsheet from a simple static file to a live, collaborative data source that automatically fuels your business intelligence dashboards, freeing you up from manual updates so you can focus on finding insights.

Establishing these kinds of automated data pipelines is critical, but learning the intricacies of different tools like Power BI can take time. At its core, the goal is to spend less time wrangling data and more time understanding it. We built Graphed to solve this problem directly. Instead of managing connectors and data models, you just connect your marketing and sales platforms in a few clicks, then ask for the dashboards and charts you need using plain English. Our AI handles building the live reports, so you can skip the technical setup and get straight to the answers.