SharePoint subsites (project sites) dynamically

[ElizabethTachji]

Hi, Is there a way I can capture Sharepoint subsites dynamically without a custom function.  In the example below, I call each subsite and then append.  Is there a way of dynamically bring in all subsites in one go.  That is I dont need to list each subsite like I have below.  thank you inadvance.

let

    Source = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1", [Implementation=null, ApiVersion=15]),

    Source1 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/58", [Implementation=null, ApiVersion=15]),

    Source2 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/84", [Implementation=null, ApiVersion=15]),     

    Source3 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/241", [Implementation=null, ApiVersion=15]),

    Source4 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/474", [Implementation=null, ApiVersion=15]),  

    Source5 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/908", [Implementation=null, ApiVersion=15]),      

    Source6 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1102", [Implementation=null, ApiVersion=15]),

    Source7 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1248", [Implementation=null, ApiVersion=15]),

     Source8 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1276", [Implementation=null, ApiVersion=15]),

     Source9 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1320", [Implementation=null, ApiVersion=15]),

     Source10 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1369", [Implementation=null, ApiVersion=15]),

     Source11 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1394", [Implementation=null, ApiVersion=15]),

    Final = Table.Combine({Source, Source1,Source2,Source3,Source4,Source5,Source6,Source7,Source8, Source9,Source10,Source11 }),

    #"Filtered Rows" = Table.SelectRows(Final, each ([Title] = "CHANGE")),

[v-yiruan-msft]

Hi @ElizabethTachji ,

It seems that you want to combine files from multiple different sub-sites. Could you try to move the files of these different sites into one folder and combine them? You can refer to the following blogs to implement the operation.

Combining Excel Files hosted on a SharePoint folder with Power BI

Combine multiple files from SharePoint folder in Power BI

Best Regards

[ElizabethTachji]

Combine folder is combining items in a particular folder.  I am wanting to combine lists in different sites.