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.

10 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

      1. I tried this method and it didn’t work on the refresh because it relies on my credential to refresh the data that also involves MFA.

        1. Hi Douglas,

          Thanks for getting in touch.
          The methods explained here are to get data from files stored on SharePoint Online which will not affect the data refresh.
          If you used your credentials when connecting to the SharePoint, then the data refresh uses your credentials. You can always change the credentials from the Dataset settings on Power BI service to switch to a different user account.
          I hope that helps.

          Cheers

  1. HI Soheil,

    What happens if i move the file into another folder. or I rename the any folder in the path. Will the link break?
    is there a way around that?

    i know Google sheets, has this idea of a doc id, so no matter where you move the doc, its always linked through that ID, does Microsoft have something similar?

    1. Hi there,

      Welcome to BIInsight.com.
      That’s a very good question.
      You made me think of writing a blog post about it indeed.
      SharePoint Online also has file ID, but it is used internally in SharePoint and I am not aware of a way that you can connect to a file (in your case an Excel file) from Power BI using their ID.
      However, there is another way that can potentially help you with your challenge.
      Instead of using the Web connector in Power BI, you can use the SharePoint Folder connector.
      Then you need to paste your SharePoint Site URL in the connector. This way you will see all files (including Excel, Word etc…) stored on that site. The next step is to filter the results based on your Excel file name and expand the Excel (binary).
      This way, you connect to the file regardless of the folder it is stored in.
      But keep in mind, if you move the file to another SharePoint Site, then your report breaks.

      I hope that helps.
      Cheers

  2. i was hitting brick walls trying to just Get Data from the input excel file in a Sharepoint (Document Sub) Folder…

    thank you Soheil for the useful tips to workaround the Root restrictions (also unhelpful as it lists EVERYTHING).

    a follow-up question – can i refresh the powerbi just by replacing the aforementioned excel file? (same filename in same location, since the Load & Transform has already been defined); thanks again Soheil!

    1. Hi Gary,

      Thanks for your feedback.
      Regarding your question, as always, it depends on your specific scenario.
      Replacing the Excel file with a new one (with the same file structure) does not automatically refresh your Power BI dataset.
      But if you scheduled an automatic refresh on the dataset, then the data will be read from the new Excel file on the next scheduled refresh.

      I hope that helps.
      Cheers.

Leave a Reply

Your email address will not be published.