How to consolidate same list from several subsites in Sharepoint

[alavanka]

Hello all,

I have several projects (subsites) in Sharepoint Online and each has a set of lists with the same structure (ex. Risks, Issues, Deliverables, Reporting Status,  etc.)

Project A

Risks

Issues

Deliverable

Reporting Status

Project B

Risks

Issues

Deliverable

Reporting Status

Project C

Risks

Issues

Deliverable

Reporting Status

.

.

Project Z

Risks

Issues

Deliverable

Reporting Status

I am struggling to create a query in PowerBI that will get the information from all the subsites combined  for each of the lists.

Using the connector Sharepoint Online List I am able to get the information of each lists for an isolated subsite, mas I am not able to retrieve the subsites from the masterpage.

What do you suggest me to solve my problem?

I really need help to sort this out... I don't understand how I can't find an out of the box solution, as it seems a basic requirement to many companies managing several data from different subsites.

Any help will be wellcome! 🙂

[v-kelly-msft]

Hi  @alavanka ,

Use Table.Combine with Table.SelectColumns to realize it.

References are as below:

https://docs.microsoft.com/en-us/powerquery-m/table-combine

https://docs.microsoft.com/en-us/powerquery-m/table-selectrows

Check my example below:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[alavanka]

Hi v-kelly-msft,

I am afraid I didn't explain properly.

I would like to have this information dinamically. To get all the subsite lists from a site and not being worried to change the query whenever we have a new project or if a project is closed.

[v-kelly-msft]

Hi  @alavanka ，

I guess the method in below blog is what you need.

https://feathersanalytics.com/dynamic-column-merging-in-power-query/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!