Loading Data from One-Drive Personal

Guides • Data Integration & ETL • 09/01/2021

Overview

There are quite a few companies that provide online document storage for free, including Dropbox, but also Google Drive and OneDrive Personal (Microsoft). This article shows how you can load data from OneDrive Personal (stored in the cloud) directly into Power BI or Excel (without the need for a gateway to refresh your workbooks once loaded to e.g. PowerBI.com). This process is a bit different to OneDrive for Business, hence this article.

The steps to complete this process are:

Save the file (typically Excel) to OneDrive Personal and Sync it online (or manually copy it online)
Navigate to the online folder containing the file via a browser.
Generate a secure URL for the file that can be used by Power BI.
Import the data from Web.

Here we illustrate this with some sample Sales data. This saved an Excel file called “ETL – One Drive Sales Data.xlsx” onto OneDrive Personal and then automatically sync’d to OneDrive Personal online.

The easiest way to find the file online is to right click on the folder containing the file in Windows explorer (the folder, not the file) and selected “view online”. If you click on the file, it will show you the file online. If you click on the folder, it will show you the file in the folder like is shown below.

The next thing to do is to get the secure URL for the file. The process is not intuitive and involves a few steps as shown below.

Obtaining the URL for the File

Click on the 3 dots (#1 below) and then on Embed (#2 below).

A panel will pop up to the right from where you can generate the embed code.

Copy the entire code and paste into Notepad for editing.

Remove all the text related to the iFrame before the URL starts, including the quotes.
Replace the word “embed?” with “download?”, keeping the ? mark.
Remove the cid part and retain the resid and authkey portion.
Remove the rest of the text after em=2.

See below. After editing the embed code, you should be left with this:

Next, add “&app=Excel” as shown below at the end of the string, like shown below:

This is the final URL for the Excel file that you can use to load into Power BI Desktop.

Loading the File into Power BI Desktop

The next step is to use this URL to load the file into Power BI Desktop.

Open a new Power BI workbook and click Get Data, and then on Web, as shown below:

You may be prompted to enter your credentials. You must select Anonymous credentials here. Power Query does not support authentication for web sources other than OneDrive for Business and SharePoint. The way it works is the link used above contains an authority key as part of the URL to give permission for the access. Be aware that anyone with this URL will be able to access the file.

You can select the data you want to load from the dialog as shown below.

If you clicked on Load, the table is loaded into Power BI Desktop, and check the data by adding a visual as shown below:

You cannot Preview the Workbook

There is a limitation on the Excel file size that can be previewed in the browser. If the file size is greater than 5 MB and you try to “preview” the file, you will get an error message.

But don’t give up – this only relates to rending the workbook via a browser, so it looks like Excel. Power Query has no issue loading these files. Just ignore this message if you see it when generating the embed code, and make sure when you connect to the file with Power Query that you do not select the Web preview. It should work and load just fine.

Other Approaches

There may be other approaches to the above for One Drive Personal files, such as using Power Automate, but the above method does work reliably.