How to Get Data from OneDrive to Power BI

Tired of manually downloading an Excel file from OneDrive, uploading it into Power BI, and repeating the process every week? There's a much better way. By directly connecting Power BI to your OneDrive files, you can create reports that update automatically, saving you hours of repetitive work and ensuring your team is always looking at the latest data.

This tutorial will walk you through, step-by-step, how to create a live connection between files stored in your OneDrive for Business and a Power BI report. We'll cover the two most common methods and point out the common roadblocks so you can get it right the first time.

Why Connect OneDrive to Power BI Anyway?

Before jumping into the "how," it's worth understanding the "why." Connecting your reports directly to cloud-based files isn't just a small convenience, it fundamentally changes your reporting workflow for the better.

• Automatic Refreshes: This is the biggest win. You can set a schedule (e.g., every morning at 8 AM) for Power BI to automatically fetch the latest data from your OneDrive file. Someone updates the spreadsheet? The changes will appear in your dashboard automatically. No more manual exports and imports.

• A Single Source of Truth: When the report is fed by a central file in OneDrive, everyone works from the same data. You eliminate the chaos of having multiple file versions saved on different desktops or sent through email threads. The file in OneDrive is the truth, and the Power BI report reflects it.

• Cloud-First and Accessible: Your data source (the OneDrive file) and your report (in the Power BI Service) are both securely stored in the cloud. This means you and your team can access and collaborate on them from anywhere, on any device.

The Most Important Step: Getting the Correct File Path

This is where most people get tripped up. The long URL you see in your browser's address bar when you open a file in OneDrive is a shareable link for viewing online - it's not the direct file path that Power BI needs to establish a data connection. Grabbing the wrong link will result in a connection error.

Here’s the surefire way to get the correct path:

1. Navigate to your file in OneDrive or a SharePoint document library.

2. Click the three dots (...) next to the file name, then select "Open" and choose "Open in app." This will open the file in your desktop version of Excel (or another relevant program).

   (Note: If you don't have the desktop application, you can still find the path. Open the file in the browser, go to File > Info, and you should see a "Copy path" button or the full path listed at the bottom of the pane.)

3. Once the file is open in the desktop application, go to File in the top-left corner.

4. In the Info screen, click the "Copy path" button right below the file name.

5. 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

6. This is the critical part: Delete the ?web=1 from the very end of the URL. The question mark and everything after it is a web parameter that will break the Power BI connection.

Your final, clean URL should look like this:https://yourcompany-my.sharepoint.com/personal/your_name/Documents/Reports/Monthly_Sales.xlsx

Now that you have the correct path, you're ready to connect it in Power BI.

Method 1: Connecting OneDrive to Power BI Desktop

This is the standard and most powerful method, as it gives you access to the Power Query Editor to clean and transform your data before building visuals. This is the recommended workflow for building robust, long-term reports.

1. Get Data from Web

Open a new or existing Power BI Desktop file. In the Home ribbon, click on the dropdown for Get Data and select Web.

2. Enter the File Path

A dialog box will appear asking for a URL. Paste the cleaned-up file path you copied from the previous section and click OK.

3. Authenticate Your Account

Power BI now needs permission to access your OneDrive account. It will prompt you with an "Access Web content" window.

• On the left side of the window, select Organizational account. Do not use Anonymous or Basic.

• Click the Sign in button and log in with your normal Microsoft 365 credentials (the same ones you use for OneDrive and Outlook).

• Once you're signed in, the button will change to "Sign out." Click Connect at the bottom of the window to proceed.

4. Select Your Data in the Navigator

After successful authentication, Power BI connects to the file and a Navigator window appears. It will show you all the available tables and sheets within your Excel workbook (or it will show a single table if it's a CSV). Click the checkbox next to the sheets or tables you want to import.

You have two choices at the bottom:

• Load: This loads the data directly into your Power BI data model as-is. Good for clean, perfectly formatted data.

• Transform Data: This is the more common choice. It opens the Power Query Editor, where you can clean data, remove columns, change data types, and perform hundreds of other transformations before loading it into your model.

Once you’ve loaded the data, you can start building charts and visuals in Power BI Desktop just like you would with any other data source.

Keeping it Fresh: Setting Up a Scheduled Refresh

Building the report in Power BI Desktop is only half the process. To unlock the automation, you need to publish it to the Power BI Service and set up a refresh schedule.

1. Publish Your Report

After saving your Power BI file (.pbix), click the Publish button on the Home ribbon in Power BI Desktop. You'll be asked to choose a destination workspace in the Power BI Service. Select your workspace and click "Select." Once it's successfully published, you’ll get a link to open the report online.

2. Configure the Refresh in Power BI Service

Navigate to https://app.powerbi.com and go to the workspace where you just published the report. You will see both a new Report and a new Dataset.

1. Find your new dataset in the list. They will have the same name as your report.

2. Hover over the dataset and click the three horizontal dots (...), then select Settings.

3. In the Settings screen, expand the Data source credentials section.

4. You'll likely see an error saying the credentials are invalid. Click the "Edit credentials" link.

5. In the pop-up, set the Authentication method to OAuth2 and the Privacy level to Organizational. Click Sign in and log in with your Microsoft 365 account one more time. This allows the Power BI cloud service to access your OneDrive cloud service.

6. Now, expand the Scheduled refresh section below.

7. Toggle the switch to "On" to activate it. You can then set the refresh frequency (Daily or Weekly), select your time zone, and add specific times for the refresh to run. (Power BI Pro accounts get up to 8 daily refreshes, while Premium accounts get up to 48.)

That’s it! Now, Power BI will automatically check your OneDrive file for updates at your scheduled times and refresh your report’s data without you having to do anything at all.

Method 2: Connecting Directly in the Power BI Service

This is a quicker, browser-only method that's great for getting data into Power BI without using the Desktop application. The tradeoff is that you don't get direct access to the Power Query Editor for heavy data transformation, but it’s perfect for simple, clean datasets.

1. Log in to the Power BI Service and navigate to your workspace.

2. Click the New button in the top-left and select Dataset from the dropdown menu.

3. On the next screen ("Create a dataset"), click the tile for Files.

4. Select OneDrive for Business from the list of file locations.

5. An embedded OneDrive file browser will appear. Navigate through your folders until you find the file you want to connect to, select it, and click the Connect button.

6. You'll be presented with a critical choice: Import vs. Connect.

   • Import (Recommended): This option pulls the data from the spreadsheet tables into a new Power BI dataset. It also creates a blank report for you. The scheduled refresh is usually set up automatically this way. This is the best option for building interactive reports.

   • Connect: This option essentially embeds your entire Excel workbook inside of Power BI. It's more like a viewer link than a true data connection and is less flexible for building new visuals.

Choose Import. Power BI will instantly create a new dataset, a blank report, and often a dashboard tile. The connection to OneDrive is already live. While the refresh is typically pre-configured, it’s always a good habit to go into the dataset settings (as described in the previous section) to confirm your credentials are set and a refresh schedule is active.

Final Thoughts

Connecting Power BI directly to data sources like a spreadsheet in OneDrive is the key to creating scalable and automated reports. By replacing the manual download/upload cycle with a live, refreshable connection, you free up valuable time and ensure every stakeholder is viewing the most current information available.

Of course, your business data often lives in more places than just OneDrive. Stitching together data from your CRM, Google Analytics, Shopify, and various ad platforms to get a complete picture can be a major challenge. At Graphed, we unified this process. We let you connect all your marketing and sales data sources with a few clicks and then use simple, conversational language to build real-time dashboards and get instant answers - no need to manage file paths or set up individual data-source credentials ever again.