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? 

 

 

 

 

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,289 guests
Please welcome our newest community members: