Scheduled Refresh with multiple SharePoint Subsites

[Anonymous]

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.

Project Name	Project Id	Project Url	Content
Project1	1	https://company.sharepoint.com/service/project1	Table
Project2	2	https://company.sharepoint.com/service/project2	Table
Project3	3	https://company.sharepoint.com/service/project3	Table

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

[V-pazhen-msft]

@Anonymous 
You can try SharePoint Folder and append the tables from different sites.

Power Query SharePoint folder connector | Microsoft Docs 

Then publish to service and set a schedule refresh to the dataset. 

Refresh a dataset from OneDrive or SharePoint Online - Power BI | Microsoft Docs

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.