Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.

Method 1: Getting the Excel File Path from the Excel Desktop App

This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.

In SharePoint Online go to the desired document library then follow these steps to make it work:

  1. Select the Excel file
  2. Click the Open button
  3. Click Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
Opening Excel file from SharePoint Online in Excel Desktop App

This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:

  1. Click the File menu
  2. Click Info
  3. Click the Copy path button
Copying Excel Path from Excel Desktop App
Copying Excel Path from Excel Desktop App

So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.

Open Power BI Desktop and follow these steps:

  1. Click Get data
  2. Click Web
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Getting data from Excel stored in SharePoint Online in Power BI Desktop
  1. Paste the path we copied from Excel in the URL text box
  2. Delete the ?web=1 from the end of the copied path
  3. Click OK
Modifying the path copied from Excel to get the data in Power BI Desktop
Modifying the path copied from Excel to get the data in Power BI Desktop
  1. From the Access Web Content page, click Organizational account
  2. Click Sign in and pass your credentials
  3. Click Connect
Passing credentials for an Organizational Account to access Excel date from SharePoint Online in Power BI Desktop
Passing credentials for an Organizational Account to access Excel date from SharePoint Online in Power BI Desktop
Important Note
If you miss step 10, you will get the following error:
Details: "The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)."
An error raises if we forget to take out the query part from the file path
An error raises if we forget to take out the query part from the file path

There you have it. Now you can select tables for sheets and start building your reports in Power BI Desktop.

Navigating the Excel data stored in SharePoint Online
Navigating the Excel data stored in SharePoint Online

Method 2: Getting the Excel File Path Directly from SharePoint (without opening the file in the Excel desktop app)

Last week I had a session with one of my customers going through the very same scenario. The customer had to teach his other colleagues how to use Power BI to get data from Excel files stored in SharePoint Online. He thought, going through all the steps explained in the first method is too much, and it might not be easy for his colleagues to absorb it. So we thought to look at our options in SharePoint Online, that he found something interesting. So this method is originally discovered by my customer, Mr Callum Fraser. Follow the steps below to get the file path directly from SharePoint Online:

  1. Select the Excel file
  2. Click the ellipsis button
  3. Click Details
  4. In the Details pane, scroll down in the pane to find the Path section
  5. Click the Copy Direct Link button
Copying Direct File Link from File Details in SharePoint Online
Copying Direct File Link from File Details in SharePoint Online

In Power BI Desktop, just click Get Data, then select Web and paste the link in the URL text box.

Getting data from Excel hosted in SharePoint Online with a Direct File Link
Getting data from Excel hosted in SharePoint Online with a Direct Link

The benefits of this method over the previous method:

  • It requires fewer clicks to get the direct link
  • The direct link does not have any query parts so we do not require to modify the link
  • The whole process is easier to remember

As always, I would like to know your opinion. Have you used these methods before? Do you know a better way to get the data from Excel files stored in SharePoint Online? Please share your thoughts in the comment section below.

3 thoughts on “Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

    1. Welcome to BIInsight.com.
      Yes it does, these methods are only to get the data from Excel files stored in SharePoint Online.
      They do not affect your capability to setup scheduled data refreshes.
      Cheers

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.