How to Get Data from OneDrive in Power BI

Connecting your Excel or CSV files from OneDrive to Power BI turns your static spreadsheets into a powerful, automatically-updating dashboard. This saves you from the tedious cycle of manually downloading and re-uploading files every time you need to refresh your report. This guide will walk you through the correct way to link OneDrive and Power BI, set up scheduled refreshes, and troubleshoot common issues.

Why Connect Power BI to OneDrive Anyway?

Working directly from a file on your local desktop means your Power BI report is only as fresh as the last time you uploaded it. By connecting to the file in OneDrive, you unlock several key advantages that make your reporting workflow much more efficient.

• Automatic Refreshes: This is the biggest win. You can schedule Power BI to automatically check the OneDrive file for updates and refresh your report. If you update the numbers in your Excel sheet, your dashboard reflects those changes without you lifting a finger.
• A Single Source of Truth: Everyone on your team can work from the same live file in the cloud. There’s no more confusion over which version of the "Final_Report_v3_Updated.xlsx" is the right one. The report is always connected to the latest version.
• Accessibility and Collaboration: Your data source lives in the cloud, meaning you and authorized colleagues can access and edit it from anywhere. This is ideal for remote teams where multiple people might need to contribute to the dataset.
• Version History: OneDrive automatically saves previous versions of your files. If someone makes a mistake or overwrites crucial data, you can easily restore an older version, and your Power BI connection will remain intact.

In short, it’s the difference between a static, snapshot-in-time report and a living, dynamic dashboard that evolves with your business.

The Best Way to Connect OneDrive to Power BI: Using the Web Connector

While Power BI has a few different options for bringing in data, the most reliable and refresh-friendly method for OneDrive files uses the "Get Data from Web" connector in Power BI Desktop. It might seem strange not to use a dedicated "OneDrive" connector in the Desktop app, but this method correctly establishes the path Power BI needs to find and refresh your file repeatedly.

Here’s the step-by-step process that works every time.

Step 1: Get the Correct File Path from OneDrive

This is the most critical step and the one where most people get tripped up. Do not use the "Share" link. You need the direct file path, and the easiest way to get it is by opening the file in its desktop application directly from the web.

1. Navigate to your OneDrive for Business account in your web browser (e.g., Chrome, Edge).
2. Locate the Excel or CSV file you want to connect to.
3. Do not right-click and copy the link. Instead, click on the file to open it in the Excel Online viewer.
4. At the top of the screen, click the button that says "Open in Desktop App" (or something similar depending on your view). This will launch the file in your local Excel application, not just the synced version in your File Explorer.
5. Once the file is open in Excel, go to File > Info.
6. Click the Copy path button. This copies the direct cloud URL of the file to your clipboard.

Step 2: Clean Up the Copied Path

The path you copied from Excel has a bit of extra text at the end that will prevent Power BI from accessing it correctly. You need to remove this.

Paste the path into a text editor like Notepad. It will look something like this:

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

You must delete the ?web=1 string from the end of the URL. The final, clean path should look like this:

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

This clean URL is exactly what Power BI needs.

Step 3: Connect Using the Web Connector in Power BI Desktop

Now, open Power BI Desktop and follow these instructions:

1. On the Home ribbon, click Get Data and select Web.
2. In the "From Web" dialog box, paste your cleaned URL into the text box and click OK.
3. Power BI will now ask you to authenticate. Since you are accessing a file within your Microsoft 365 organization, do not use "Anonymous". Select Organizational account from the left-hand menu.
4. Click Sign in and use your company Microsoft/OneDrive login credentials. You are giving Power BI permission to access the file on your behalf.
5. After successfully signing in, click Connect.

Step 4: Load and Transform Your Data

Once connected, the Power BI Navigator window will appear, just like it does for any other data source. You’ll see a list of the available tables and sheets within your Excel file.

• Select the tables or sheets you want to import.
• You can click Transform Data to open the Power Query Editor and clean up your data (e.g., rename columns, change data types) or click Load to bring it directly into your Power BI data model.

That's it! Your OneDrive file is now connected to your Power BI report.

Setting Up a Scheduled Refresh in the Power BI Service

Connecting the data is only half the battle. The true power comes from automating the refresh process so you never have to think about it again. You set this up in the Power BI Service (app.powerbi.com) after you’ve published your report.

1. After building your report in Power BI Desktop, publish it to a workspace in the Power BI Service.
2. Log in to app.powerbi.com and navigate to the workspace where you published your report.
3. Find the dataset for your report (it will have the same name). Hover over it, click the three-dot menu (...), and select Settings.
4. Expand the Data source credentials section. You will see a notice that the credentials are not valid. Click Edit credentials.
5. A pop-up window will appear. The authentication method should be set to OAuth2. Just click Sign in and re-enter your organizational account credentials. This confirms to the Power BI Service that it has ongoing permission to access your OneDrive file.
6. Next, expand the Scheduled refresh section. Toggle it to On.
7. Configure your desired refresh frequency. You can set it to daily or weekly, select your time zone, and add specific times for the refresh to run. You can add multiple times per day (Pro users get up to 8 daily refreshes).
8. Click Apply.

With that done, Power BI will automatically check your OneDrive file according to the schedule you set. Any changes made to the data in that file will be pulled into your dataset and will instantly be reflected in all related reports and dashboards.

Common Problems and Quick Fixes

Even with a perfect setup, you might run into a few common hurdles. Here’s how to solve them.

Problem: "Web.Contents failed to get contents... (403): Forbidden"

This is the most frequent error. It almost always means you've used the wrong URL.

• Solution: You either used a "share" link or you forgot to remove the ?web=1 parameter from the end of the URL. Go back and carefully follow Step 1 and 2 to get the direct file path and clean it properly.

Problem: "We were unable to connect because this credential type isn't supported for this resource."

This authentication error happens when connecting in Power BI Desktop.

• Solution: You likely selected "Anonymous" or "Basic" for the credentials. You must use Organizational account and sign in with the relevant Microsoft 365 account to grant access to the file.

Problem: Scheduled Refresh is Failing

Your test refresh works, but the scheduled ones keep failing with an authentication error.

• Solution: Go back to the dataset settings in the Power BI Service. Under "Data source credentials," click Edit credentials and sign in again with OAuth2. Sometimes the authentication token expires and simply needs to be re-authorized. Also, ensure the source file hasn't been moved, renamed, or deleted in OneDrive, as that will break the path.

Final Thoughts

Connecting your OneDrive files directly to Power BI establishes a seamless and automated data pipeline, freeing you from manual updates and ensuring your reports are always built on the most current data. By using the "Get Data from Web" method with the correct file path, you create a robust connection that fully supports scheduled refreshes in the cloud.

While Power BI is an incredibly powerful tool, we believe getting insights from your data shouldn't be a complicated, multi-step process. Here at Graphed, we're building a simpler way to connect all your business data—from Google Analytics and Facebook Ads to Shopify and Salesforce—in one place. You can use simple, natural language to ask questions and instantly build live dashboards, skipping the complex setup so you can get straight to the insights you need to grow your business. You can check out Graphed for free and spend your time acting on data, not just fighting with it.