Get Excel Data into Power BI using SharePoint

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 SharePoint. For the OneDrive personal article, see Get Excel Data into Power BI using OneDrive Personal.

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 SharePoint

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

Step 1. Upload your file to SharePoint

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

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

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 the SharePoint folder containing the file.
  2. Choose the ⋮ button and select Details.
  3. Click on the More Details section on the right-hand side.
  4. Click on the Copy button next to the Path item.

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

https://<subdomain.sharepoint.com>/sites/<sitename>/Shared%20Documents/<folders>/<filename>

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.