Get Excel Data into Power BI using OneDrive Personal

This article will assist you to get Excel data into Power BI on the Cloud.

If you’ve ever tried getting Excel data into Power BI, you might have found that while it works on the desktop, it fails online and complains about errors.

This is Power BI giving you a cryptic clue that the data from Excel cannot be accessed.

The easiest way around this is to put your Excel file in either SharePoint or OneDrive Personal.

This article will assist you to get Excel data into Power BI using OneDrive Personal. For the SharePoint article, see Get Excel Data into Power BI using SharePoint.

Note that these steps are not required for OdataLink. OdataLink is already cloud based and can be used directly within Power BI Deskstop and Online without any changes to your Power Queries. This article is only intended for those that store additional data in Excel.

Understanding the Problem

There is a simple reason why getting data from Excel into Power BI on the cloud doesn’t work. The Excel file you browse to is located on your machine.

This path is stored in the Power Query.

This works perfectly when you use Power BI Desktop. Your computer has access to the file. However, it will not work for others unless they also have access to the same file location (on a server, for instance).

More importantly, this will not work for Power BI online. It simply does not have access to your machine or it’s files.

Get Excel Data into Power BI using OneDrive Personal

To pull data from Excel into Power BI on the Cloud requires you to put the Excel file on the cloud. Luckily, OneDrive Personal can be used for this.

The only downside to OneDrive Personal over Sharepoint is how complex it is to configure.

Step 1. Upload your file to One Drive Personal

The first step is simple. You need to upload your Excel file to OneDrive Personal.

Step 2. Get a cloud link to the Excel file hosted on OneDrive Personal

Once the file is uploaded, you need to create a URL that can be used to provide access to the file. To do this, follow these steps.

  1. Go to https://onedrive.live.com/ and locate the file.
  2. Choose the ⋮ button and select Embed.
  3. Copy the embed text on the right-hand side of the screen to notepad or another text editor.
  4. Extract the URL to the file from the copied text.
    The URL is contained within the SRC attribute. The attribute will look something like the text below. You want to keep anything between the leading and trailing double quote (") highlighted in blue.
<iframe src="https://onedrive.live.com/embed?cid=<cid>&resid=<resid>%21178&authkey=<authkey>&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>
  5. Replace the word embed with download.
  6. Append &app=Excel to the end of the url.

You now have a URL to use to locate the file. It will look something like this.

https://onedrive.live.com/download?cid=<cid>&resid=<resid>&authkey=<authkey>&em=2&app=Excel

Step 3. Create or Update your Power Query to use this new location.

Create a new Power Query to get data from Excel hosted on OneDrive Personal

To create a new Power Query to an Excel file hosted on OneDrive Personal follow these steps.

  1. Select Get Data, then Web.
  2. Paste the URL and click OK.
  3. Tick the tables you want to include and select Transform.

If no tables appear, you may need to create a blank query and set the formula for the first step manually. To do this, follow these steps.

  1. Select Get Data, then Blank Query
  2. Enter the following formula in the Power Query formula bar.
    = Excel.Workbook(Web.Contents(<url>), null, true) 
where <url> is the URL created previously.

Updating existing Power Queries to get data from Excel hosted on OneDrive Personal

If you already have existing queries connected, you can update them to use the Excel data hosted on OneDrive Personal. Follow these steps.

  1. Edit the Power Query
  2. Select the first step in the power query.
    The first step should look something like this.
    = Excel.Workbook(File.Contents(<filepath>), null, true))
  3. Replace it with the following text.
    = Excel.Workbook(Web.Contents(<url>), null, true) 
    where <url> is the URL created previously.
  4. Refresh the Power Query and audit each subsequent steps to ensure they still work.

Step 4. Publish to the cloud

Once all Power Queries to Excel has been created and/or replaced, publish your dashboard to Power BI online and test.