Forum Discussion
PowerBI and Data from SharePoint lists across multiple sites
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?
ercwebdev sorry for missing your message. I'm having trouble digging up an old version of my report which used this method but it sounds to me like there may be an issue with some of the references in your table... is it possible one of the URLs is pointing back to the parent site rather than a subsite?
I was able to get this to work in Power BI desktop using the method I outlined in my previous post, however when I published the report, Power BI Service and Dataflows both wouldn't refresh and I wasn't able to find a way to make it work (the error was due to the dynamic nature of my query). If you can live with refreshing it locally, it should work as expected. I had to restructure the way I was approaching my problem and moved as much of the data to the parent site as possible, making the subsites much less useful.
