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

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

4. Click the File menu
5. Click Info
6. Click the Copy path button

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:

7. Click Get data
8. Click Web

9. Paste the path we copied from Excel in the URL text box
10. Delete the ?web=1 from the end of the copied path
11. Click OK

12. From the Access Web Content page, click Organizational account
13. Click Sign in and pass your credentials
14. Click Connect

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)."

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

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

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

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.