Loading a File from OneDrive Personal into Power BI Desktop

I am sure you know, there are quite a few companies that provide online document storage for free, including Dropbox (which is my personal favourite) but also Google Drive and OneDrive Personal (Microsoft). This article today shows how you can load data from OneDrive Personal (stored in the cloud) directly into Power BI without the need for a gateway to refresh your workbooks once loaded to 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.

I will illustrate this with some sample data that I created using the Adventure Works database. I saved an Excel file called “Test File.xlsx” onto my OneDrive Personal on my PC and it was 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.

This took me to the following view in my browser.

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 I show below. It’s easy once you know how.

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 (#1 below).

Copy the entire code (#1 above) 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.

You will be left with the following.

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

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. I opened a new Power BI workbook and clicked on Get Data (#1 below) and then on Web (#2 below).

You will probably 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.

I copied the URL that I got in the notepad and pasted as shown below and clicked OK.

From there the rest is pretty standard. You can select the data you want to load from the dialog as shown below.

I selected the table and clicked on Load. The table is loaded into Power BI Desktop. I checked the data adding a visual as shown below.

You can’t 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 that 5 MB and you try to “preview” the file, you will get an error message as shown below.

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 will work and load just fine

Variations to the Approach

Actually, in my testing I tried quite a few variations to the exact steps above that worked, kind of. Sometimes a slightly different approach will throw an error message but yet it then it seems to work just fine. I found the above combination to be the most reliable, but you may find other ways that work, too.