How do I aggregate lists across Project online sites?

Question

I am trying to extract data out of Microsoft Project Online, using PowerQuery in both PowerBI and Excel.

This is more of a PowerQuery / SharePoint question that specifically Project Online.

So I can the list of "Project sites" which is, I understand, a separate SharePoint site for each "project"

let
 Source = OData.Feed(SiteBaseURL & "_api/ProjectData/Projects", null, [Implementation="2.0"]),
 #"Removed Other Columns" = Table.SelectColumns(Source,{"ProjectIdentifier", "ProjectWorkspaceInternalUrl"}),
in
 #"Removed Other Columns"

which produces

https://............sharepoint.com/sites/edison/Project 1
https://............sharepoint.com/sites/edison/Project 2
https://............sharepoint.com/sites/edison/Project 3

So now, each of those SharePoint sites has a their own "lists" - probably 99% the same names on each site, but nonetheless, all separate sites.

So how do I get a complete aggregate all lists on all the sites listed above?

I have two problems:

Permissions. I am able to access the URL (" https://....sharepoint.com/sites/edison/Project 1") if I just paste it into my browser. But PowerQuery tells me access denied. (This is a single sign on with tokens environment)
How do I code the formula that iterates through a lists and builds a list of lists?

O A O A · Accepted Answer · 2021-04-28T10:11:27

If all the sites share a parent, you can use the Sharepoint search/query rest api. It looks at the managed properties of the lists in parent and the subsites, then provides the data stored in them.

How do I aggregate lists across Project online sites?

1 Answers