Dynamically connect to SharePoint Online List sources based on a list of urls

[sriram_in]

I have a requirement where we have the same list exists across many sharepoint sites and I have the list of those URLs, this list will change whenever a new site is created. Can we take this URL list as input and connect to each of the sharepoint site and get the required data?

 So whenever a report refresh happens, it has to fetch the list of URLs and connect to all the sites listed and get data from list specified and append it together to one table.

[v-shex-msft]

Hi @sriram_in,

You can use below function.(it used to connect to each url and merge them to one table)

let
    LoadSharepointList=(source as table, columnName as text) as table =>
let
    CombineTable= Table.Combine(Table.AddColumn(source, "Custom", each SharePoint.Files([columnName]))[Custom])
in
    CombineTable
in
    LoadSharepointList

Regards,

Xiaoxin Sheng

[sriram_in · Accepted solution]

Hi @v-shex-msft,

Thanks much for your reply, I can see this will combine the files, I want to be able to do it for Custom lists in SharePoint. 

Let us say I have 3 sites and I have the URLs in a separate list, the function below will be able to get the URLs but after that how do I navigate to the specific list in all the sites by list name and combine them.

Regards,

Sriram S

[muneebch041]

HI 

You can expand your custom column.