Static Data Breaks Refresh of SharePoint Report

[dparkinson]

Hi,

I created a report in PowerBI desktop that uses data from SharePoint online.  When I publish this to the service, I can set it up to refresh daily and all is well in the world.

I recently added a local Excel spreadsheet with historical (static) data to provide some historical context to the reports.  I have also turned off "Include in Report Refresh" for this data source.

However, when I publish this report to the service, it will no longer allow me to refresh, presumably because of the Excel spreadsheet (which I don't want refreshing).

Any thoughts on how to get around this greatly appreciated.

Many thanks,

D.

[v-yulgu-msft]

Hi @dparkinson,

If a single dataset contains both local file and online source, to enable auto refresh or schedule refresh, you need to configure gateway for that local data source. Otherwise, the schedule won't work. Here is a similar thread for your reference.

But, it is not possible to only update the SharePoint online source while keep the local Excel spreadsheet static. As when we refresh the dataset, all data inside it will be updated.

As a workaround, you could try to store those historical (static) data in an extra excel file, you can store this file in SharePoint online also. In that case, all data (static and dynamic) is fetched from sharepoint online, after publishing to service, the dataset can be refreshed automatically. Alternatively, if don't upload this excel file to sharepoint, you could configure gateway for this file.

Regards,
Yuliana Gu