Aggregate SharePoint Lists with the same name across subsites into one big table

[Gabe_V1]

Hello!

I found a post that starts to answer my question, but not quite.

Here is my problem: my organization has SharePoint set up so that for every new MS Project project they undertake, a new SharePoint subsite gets created. For example:

https://projectportal.myorg.com/PWA/Project_1/

...

https://projectportal.myorg.com/PWA/Project_n

Each of these subsites has a SharePoint list in it, and this list is named the exact same thing in each new subsite.

I currently have the following query with the Source = SharePoint.Tables("https://projectportal.myorg.com/PWA/Project_1/", [ApiVersion = 15) that accesses the list on one site:

let
    Source = SharePoint.Tables("https://projectportal.myorg.com/PWA/Project_1/", [ApiVersion = 15]),
    #"65ff4597-5576-4264-a638-d993c7146ea1" = Source{[Id="65ff4597-5576-4264-a638-d993c7146ea1"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"65ff4597-5576-4264-a638-d993c7146ea1",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

However, I don't want to create a brand new query for each new project subsite every time a new project is initiated. Instead I'd like to be able to cycle through each subsite, find the list, and aggregate it so that once I've cycled through all the subsites I'll have one table with rows consiting of the contents of all the lists with the same name across subsites. 

Worth noting: since we are using the project names for the subsites, and we are using MS Project Web App (PWA), I do have the ability to pull the MSP Projects table and use that as an up-to-date list of all project names, and thus could use that as the source for the names of all the subsites.

I just don't know how to go about it, so any help would be greatly appreciated.

Thank you!

[Gabe_V1 · Accepted solution]

Sharepoint.Contents didn't quite work, but I found this article: Aggregating SharePoint List Data in Power BI - Marque360 that helped a lot. Adapting it, it turned out to be a 2 parter:

A function:

(siteURL,listname) =>

let

Source = SharePoint.Tables(siteURL, [ApiVersion = 15]),
#"MyListData" = Source{[Title=listname]}[Items]

in

#"MyListData"

a query with all the subsite names to reference it and pull back the list results:

let
  Source = Projects
    #"Added Custom"= Table.AddColumn(Projects, "List", each GetList([Subsite URI], "The List")),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"List"}),
    #"Expanded List" = Table.ExpandTableColumn(#"Removed Errors", "List", {"A column", "Another column"})
in
    #"Expanded List"

 So far it seems to be working well, other than that it takes forever to load because it has to cycle through EVERY subsite in the list.

[lbendlin]

How do you plan to harvest the list of subsites?  Have you considered using Sharepoint.Contents ?

I assume you are aware that you will end up with duplicate item IDs across these lists.

[Gabe_V1]

Hi @lbendlin , I have access to the MSP Projects table, MSP_EpmProject_UserView, so I could use the ProjectName column as my subsite list. I'm not familiar with Sharepoint.Contents. Does it allow for cross-subsite views?

Regarding duplicate Item IDs, if I have an index column that automatically increments with each new row, I can use that as a new Item ID for purposes of having a unique record identifier. I could also append a prefix or suffix of, say, the project name or MSP project GUID to each subsite list's row ID before appending it to the main list of lists. Just thoughts, but I'm open to better suggestions.

[lbendlin]

not familiar with Sharepoint.Contents. Does it allow for cross-subsite views?

I believe so. I don't have subsites to test with but please give it a try.