Table of Contents
When you first get started with Power BI Desktop, it’s common to connect to an Excel file as the data source for your report. In Power BI Desktop, you simply select Excel Workbook, choose a file from your local machine or OneDrive, click Load, and start building your report.
Refresh option disabled for semantic model
After publishing the report to a Fabric workspace, you might run into an unexpected issue. When you open the settings of the semantic model, you may notice that under Refresh the refresh option is greyed out and unavailable, preventing the model from being refreshed.
Under Gateway and cloud connections in the settings, you may also see a warning stating that no personal gateway is installed. This often raises the question: what went wrong, and why can’t the published report refresh automatically? And should you install a personal gateway?
The short answer: NO!
To understand what’s happening, the first step is to open Power Query by clicking Transform data in Power BI Desktop.
In Power Query, select the query that is based on your Excel file from the Queries pane on the left. Then, in the Applied Steps pane on the right, click the Source step.
In the formula bar, you’ll see that the Excel file is referenced using a local file path on your computer, for example: C:\Users\name\OneDrive\Documents\test file.xlsx.
Because this file resides on your local computer, the Microsoft Fabric refresh service—which runs entirely in the cloud—cannot access your laptop to retrieve the data from that file.
Do not use a gateway!
While installing a personal gateway might seem like a solution, it would require your computer to be continuously powered on and connected to the internet for Fabric to access the file. Using a personal gateway is generally not considered best practice, and ideally this option will be phased out by Microsoft in the future.
Connect to Excel on SharePoint or OneDrive
So how do you connect to an Excel file stored in SharePoint or OneDrive?
Follow these steps:
- If you’re using Windows 11 and the file is stored in OneDrive, navigate to your Documents folder, right-click the file, select Show more options, and choose View online.
Alternatively, if you already have the direct link, open the SharePoint or OneDrive folder containing the Excel file in your browser.
- In SharePoint or OneDrive, locate the folder and select the Excel file.
- On the right-hand side, click the Details button.
- In the details pane, scroll down to find the option to Copy path and click the copy button.
- Open Power BI Desktop, click Get data, and select Web.
- Paste the copied URL and click OK.
- If prompted, authenticate using your organizational account. The Excel file will then load into Power BI Desktop.
- After publishing the report to a workspace, you’ll be able to schedule a refresh. You may still need to configure credentials in the semantic model settings under Data source credentials.