PowerBI and Data from SharePoint lists across multiple sites

[httpftp123]

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!

[Anonymous]

Hi, 

I would like to know if anyone got to the bottom of this topic please and have a solution ? The link to the marque360 seems to be explaining exactly what i need to do, but it doesnt work when i try - expression syntax error. 

Cheers

JL

[v-qiuyu-msft]

Hi @httpftp123,

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

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 Queries features. When you have one or more columns that you'd like to add to another query, you merge the queries. When you have additional rows of data that 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.

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

If you want to combine another new SharePoint list into the existing one, you need to open another connection and open Query Editor to merge or append again. Then republish the report to service.

Best Regards,
Qiuyun Yu

[httpftp123]

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?

[v-qiuyu-msft]

Hi @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

[danderson2692]

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

[ercwebdev]

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 

[RENEALVAREZM]

I have the exact same situation. Were you able to solve this need?

---

---

[danderson2692]

@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.

[ercwebdev]

Thanks for your reply, I have since found this tutorial which I am trying. Seems to be the way forward:

https://marque360.com/aggregating-sharepoint-list-data-in-power-bi/

Thanks

Alan