Loading a File from OneDrive Personal into Power BI Desktop - Excelerator BI

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.

21 thoughts on “Loading a File from OneDrive Personal into Power BI Desktop”

  1. Thank you so much. This helped me a lot with personal report when I am unable to use sharepoint as a file location.

    Would you be able to assist in getting Google TagManager data into PowerBI?

  2. This tip is just fantastic. I Tried this and imported the data using power querry in an excel file. And it solved my issue.

    I searched for hours to find a solution. But you explained it beautifully
    Thanks a ton

  3. Prashant Panchal

    Hi matt, an amazing and very helpful article, could you please also share tips on how to connect folder/sub folders instead of files within onedrive personal?

  4. Hi Matt. Is there a way to list all the files in a folder in personal OneDrive that someone else has shared with me? I cannot seem to do this.

    1. Sorry, I’m not sure. When you connect to SharePoint, there are some columns of metadata that can be expanded. If this info is available in OneDrive Personal, then I would expect a similar metadata column. Look for a column that contains records or tables. If you fine such, then expand them and see what you can find. If you can’t see them, then I doubt it’s possible

  5. Hi Matt
    is it possible to use this method to address an absolute path to a FOLDER in my onedrive?
    Thank you so much

    1. good question – I actually don’t know. You would need to investigate using the information above an test it. Sorry, I don’t have the time to look into it, but please post back here if you find an answer.

    1. I tried to email you Sam, but the email address you provided bounced. If you would like to discuss further, please contact me via the contact page and provide me an email address where I can contact you.

  6. I have the Dutch Windows explorer and I don’t have the Embed option. I have instead an option instead Automate with flows….

  7. Thanks a lot, Matt! I’ve been searching for an online platform to replace Google Sheets which seems to be having an issue connecting with Power BI.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top