Forum Discussion
PowerBI and Data from SharePoint lists across multiple sites
Thanks v-qiuyu-msft - this is very helpful.
To avoid republishing the report every time a new site/lists is added, is there a way to dynamically iterate through all child subsites given a parent url? If not, is the only alternate solution consolidating the lists data into an external DB and generating the report from there?
Community SupportHi httpftp123,
Based on my test, we are not able to retrieve list data use SharePoint Folder, SharePoint List or Web data source by typing root site URL. In your scenario, if you don't want to republish report after new site added, you can try to export and combine lists data into SQL Server database or Excel, then use Power BI desktop connect to the SQL Server database or Excel. After publish the report to service, set the schedule refresh for the dataset. You need to make sure data in SQL Server database or Excel can be updated always.
Best Regards,
Qiuyun Yu
Hi all,
I've found a way to make this work dynamically so you don't have to create a data source for each subsite's list. In my case all of the lists that I want to pull are named "Manage Charge Codes" and I'm working out of a PWA site so I have a list which gives me the url of all subsites. Once you have that column (named [Project Site] below), you can use the following formula to add a column which can then be expanded into each line item in the source list. I have ~200 subsites which each contain a list of ~1-5 values and which aren't causing loadtime issues using this method.
= Table.AddColumn(#"Renamed Columns2", "IO List", each SharePoint.Tables([Project Site], [ApiVersion = 15]){[Title="Manage Charge Codes"]}[Items])Hope this helps,
David
Hi David,
I have a similar issue where I need to get values from the same List that is across all of the PWA subsites. I've tried using your query, but just can't get it working. I get this error 'Expression.Error: A cyclic reference was encountered during evaluation'.
Can you share any info on the set up and how you got this working?
Thanks
Alan
I have the exact same situation. Were you able to solve this need?