Can we add multiple sharepoint lists dynamically through some parameter and merge their data ?

[hrsingla88]

Suppose we have multiple different SharePoint sites with same data schema and I want to query all of them dynamically based on say an initial query with the list of sites and merge the data. Is it possible ?

[dhillon]

Did you ever find a solution to schedule refresh ?

[ImkeF · Accepted solution]

Yes, turn your query into a function with the URL as parameter.

Then create a table with one column, containing the adresses in the rows. Then add a colum where you call your function and pass the reference to this column with the URL as a parameter.

You can then expand this new column and will have all your data in one table, with one additional column: The source where it came from (your URL).

[joris]

I can't seem to get this to work. I created a new column based off the URL (as a parameter: https://company.sharepoint.com/) and the customerID, but it doesn't give me the option to expand it

[ImkeF]

Does this help?: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi...

[joris]

I missed the step where you turn the query into a function. I've tried this but no luck so far. 

So I start with connecting to my source URL via sharepoint list connector (eg https://company.sharepoint.com/sites/internal/)

and then I turn that table into a function? 

[joris]

nevermind, I figured it out. 

This video was very helpful in explaining how to use the function in combination with a URL:
https://www.youtube.com/watch?v=Ar_fV_oXSNs

this ended up being the function: 

(Custom_Parameter as text)=>
let
Source = SharePoint.Tables("https://Company.sharepoint.com/sites/internal/"&Custom_Parameter,[ApiVersion = 15])
in
#"Source"

From thereon, I was able to create a new column using this function and combining it with the customerIDs.

Very slow performance though...

[Anonymous]

Hi all,

i managed to merge sp list . But i am unable to setup schedule refresh. 

Has anyone got same issue ?

Thank you