Forum Discussion

httpftp123's avatar
httpftp123
Frequent Visitor
9 years ago

PowerBI and Data from SharePoint lists across multiple sites

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. W...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
    httpftp123
    Frequent Visitor
    9 years ago

    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's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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