Aggregate lists from SharePoint sites

[CDavies]

Hi

This question does not seem tot have a decent answer for, either that or my google has broken 🙂

I need to return a list of sharepoint subsites from a root site that have a list with a standard name.

I've tried https:// MySPSite/sites/aaaa/bbb/api/web/lists/GetBytitle('cc') which gives me data for (cc) from a single subsite list (bbb), but I need to aggregate all of these standard lists (cc) from many subsites into a single list.

Any ideas?

Many thanks

Charles

Edit: Power BI Report Server/Desktop Jan 2020.  SharePoint 2013 (soon to be 2019)

[v-juanli-msft]

Hi @CDavies 

As tested with Sharepoint online list connector, i can't connect to all lists inside all subsites at one time.

It may need to connect to lists inside one subsite  and connect to lists inside another subsite.

Best Regards

Maggie

[CDavies · Accepted solution]

To any one else who needs this I managed to get his working with a two stage approach:

1.  Create a list in SharePoint with the names of the subsites that the users can manage

2.  Used a function in Power Query to return all of the Lists from within the subsites from 1.

Thanks