PowerBI and Data from SharePoint lists across multiple sites

Question

Hello - I have 50+ (and continually growing) SharePoint sites with the same set of SharePoint lists with data on each individual site which I want to consolidate and visualise using PowerBI.

1. What is the most efficient (and best practice) method of consolidating the data into one dashboard?

2. Will I need to update the consolidated dashboard manually to include new sites are they are provisioned through the year?

Thanks in advance!

v-qiuyu-msft · Answer

Hi httpftp123,
&nbsp;
1. What is the most efficient (and best practice) method of consolidating the data into one dashboard?&nbsp;
&nbsp;
In my opinion, you can open multiple connections to connect to multiple SharePoint sites in Power BI Desktop, then you can open Query Editor to use Merge Queries or Append&nbsp;Queries features. When you have one or more columns that&nbsp;you'd like to add to another query, you merge the queries. When you have additional rows of data that&nbsp;you'd like to add to an existing query, you append the query. After you create a .PBIX report, you can publish it to Power BI Service.
&nbsp;

&nbsp;
2. Will I need to update the consolidated dashboard manually to include new sites are they are provisioned through the year?
&nbsp;
If you want to combine another new SharePoint list into the existing one, you need to open another connection and&nbsp;open Query Editor to merge or append again. Then republish the report to service.
&nbsp;
Best Regards,Qiuyun Yu

httpftp123 · Answer

Thanks&nbsp;v-qiuyu-msft&nbsp;- this is very helpful.&nbsp;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?

v-qiuyu-msft · Answer

Hi httpftp123,
&nbsp;
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&nbsp;report to service, set the schedule&nbsp;refresh&nbsp;for the dataset. You need to make sure data in SQL Server database or Excel can be updated always.
&nbsp;
Best Regards,Qiuyun Yu

anonymous · Answer

Hi all,&nbsp;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.&nbsp;= Table.AddColumn(#"Renamed Columns2", "IO List", each SharePoint.Tables([Project Site], [ApiVersion = 15]){[Title="Manage Charge Codes"]}[Items])Hope this helps,David

ercwebdev · Answer

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.&nbsp; I get this error 'Expression.Error: A cyclic reference was encountered during evaluation'.&nbsp;&nbsp;Can you share any info on the set up and how you got this working?ThanksAlan&nbsp;

Forum Discussion

PowerBI and Data from SharePoint lists across multiple sites

9 Replies

Related content

Merge data from multiple excel files from multiple sharepoint folders

Connecting Sharepoint Folder to PowerBI

PowerBI not showing folders from sharepoint.

How to combine SharePoint List from multiple SharePoint sites

Creating multiple Audiences in PowerBI App