Scheduled Refresh with multiple SharePoint Subsites

Hi all,

I need help with importing multiple SharePoint Subsite Tables and setting a scheduled refresh.

In Power BI Desktop Iuse a configuration file with all Projects, Project_Ids and SharePoint Project Urls. Then we use for every SharePoint-Url the SharePoint.Tables Function to import all Data for each Project Subsite and import these Tables in a new Column. Similar to this approach.

We use this Table to Combine all Project Subsites and import the Data for common Tables.

In Power BI Desktop we can use this way and refresh manually. As soon as we publish the report on the service we get the dynamic error and the scheduled refresh can not be implemented.

We tried the Web.Contents approach but we can not authentificate to the subsites by using the [RelativePath] option.
The use of an gateway seems not to work beacause of the dynamic error nature.

We would like to stay as dynamically with using a configuration File and the SharePoint URLs and implementing the Subsite-Tables and also using the scheduled refresh.

Does anybody have an idea to overcome this problem, or does anybody has a different implementation idea we could use.

Thanks in Advance