How do I connect to an Excel file on SharePoint using Power BI or Power Query?

When getting started with Power BI, you might connect to an Excel file to build your report. You simply open Power BI Desktop, click the Excel workbook button, choose your Excel file from My Documents or OneDrive, and click load and you create your beautiful report.

Refresh option disabled

After publishing your report to a Fabric workspace and going to the settings of you semantic model, you may find that under the settings section Refresh the refresh option for the semantic model is greyed out and disabled.

Under the settings section Gateway and cloud connections you will also see a warning that you do not have a personal gateway installed. So what happened and why can't you refresh your published report automatically? Should I install a personal gateway? NO!

Why?

To understand what happened, we first need to go to Power Query, which you can get to by clicking the Transform data button in Power BI Desktop.

In Power Query in the left Queries pane, click on the query that derived from you Excel file.

Then in the right Applied Steps pane click on the Source step.

In the formula bar you will see that the path to your Excel file is a path on your local computer. For example C:\Users\name\OneDrive\Documents\test file.xlsx.

While this file is on the local drive of your computer, Microsoft Fabric and the refresh service is a cloud based and Fabric cannot connect to your laptop to extract the data from the specific file.

Do not use a gateway!

You might be able to solve it by installing a personal gateway, but your computer has to always be on and connected to the internet for Fabric to reach it. But installing a personal gateway is not best practice and hopefully Microsoft will get rid of this option in the future.

Connect to Excel on SharePoint or OneDrive

So how can I connect to the Excel file that is saved in SharePoint or OneDrive?

You need to take the following steps:

1. When you are using Windows 11 and your file is on OneDrive, you can go to your Documents folder on you computer and right-click on the file, click Show more option and click View online.
   
   

   
   
   
   
   
   
2. Or, when you know the direct link, go to the SharePoint or OneDrive folder where you Excel file is saved using your browser.
3. In SharePoint or OneDrive you will see you folder and saved file.
4. On the right side you see the Details button
   
   
   
   
   
   
   
   
   
5. Click on the button, select the Excel file and scroll down in the details pane. You will see a option to copy the file's path. Click the copy button.
   
   
   
6. Now open Power BI Desktop, click the Get data button and click Web.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
7. Paste the URL copied from the details pane and click the OK button.
   
   
   
   
   
   
   
   
   
8. You might need to authenticate using your Organizational account. After that the Excel file should load into Power BI Desktop.
9. After publishing your report to a workspace, you are now able to schedule a refresh. But you probably need to add your credentials to the connection in the semantic model settings under the section Data source credentials.
   
   

Read More