Scheduling automatic refresh of data sources to power bi reports/app that I own - option greyed out/unavailable

Hello Jo,

Greetings!

The scheduled refresh option is almost certainly greyed out because of how the Excel tables are connected, not because you need to convert anything to SharePoint Online lists. The most common cause of this exact symptom is that the Excel tables were added using the Excel connector pointing at a local file path, for example a OneDrive or SharePoint synced folder on your PC like C:\Users\YourName\OneDrive - Company\Folder\file.xlsx. Even though that file lives in SharePoint Online, Power BI still treats a connection made this way as an on premises file, which is why the refresh option becomes unavailable unless a gateway is configured.

Here is how to resolve this without converting your Excel tables to SharePoint lists.

1. Check how each Excel table is actually connected. Open the report in Power BI Desktop, go to Transform data > Data source settings, and look at the source path for each Excel query. If the path starts with a local drive letter (C:) or a UNC path, that source is being treated as on premises even if the underlying file sits in SharePoint Online.

Reconnect the Excel sources using the SharePoint Folder connector instead of a local file path. In Power BI Desktop, use Get Data > SharePoint Folder (or Get Data > Web with the SharePoint.Contents function), enter the root site URL of your SharePoint site, and then browse to and select the specific Excel files in Power Query. This makes Power BI treat the Excel data as a true online source rather than a local file.

Site URL example: https://yourtenant.sharepoint.com/sites/yoursite

Republish the report after reconnecting. Once all three Excel tables use the SharePoint Folder or Web connector pointing at the SharePoint Online URL, and your three SharePoint sources are already connected as Online sources, your entire semantic model qualifies as cloud only. At that point you will no longer need a gateway at all and the automatic refresh option should become available.

Set the data source credentials. After republishing, go to the workspace, open the semantic model's Settings, and under Data source credentials select Edit credentials for each source. Sign in with OAuth2 using your Microsoft 365 account. This step is required before the refresh schedule toggle becomes selectable, so do not skip it even if the option looks available.

Enable Scheduled refresh. In the same settings page, expand the Refresh section, turn on Configure a refresh schedule, and choose your frequency and time slots. Up to eight refreshes per day are supported on shared capacity for non Premium workspaces.

Only use an on premises gateway if a source is genuinely on premises, for example a database or file share inside your corporate network that Power BI cannot reach directly. Since everything you described is in SharePoint Online and Excel files associated with it, you should not need a gateway once the connectors are corrected in step 2. If you do find you need one later, gateway access works like this: someone with gateway admin rights installs the On-premises data gateway (standard mode for shared use) from the link below, then adds your account under the gateway's Users tab in the Power BI admin portal so you can select it during scheduled refresh setup.

https://www.microsoft.com/en-us/download/details.aspx?id=53785

For reference, here is the official guidance on which sources need a gateway and how scheduled refresh is configured.

https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data

If this answer helps you kindly accept the answer which will help others who have similar questions.

Regards,

Sangeetha Kesavan