cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hrsingla88 Frequent Visitor
Frequent Visitor

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

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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).

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
Super User
Super User

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

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).

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




joris Frequent Visitor
Frequent Visitor

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

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

 

 

Super User
Super User

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

joris Frequent Visitor
Frequent Visitor

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

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? 

 

 

 

 

Highlighted
joris Frequent Visitor
Frequent Visitor

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

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...