Sharepoint connection with Web Contents

[JOAOLBMARTINS]

Dear all, how are you?

I'm working with a project which I need to access a few Sharepoint Lists that are hosted in different Sharepoint SubSites (despite being different subsites, all of them has the same root).

Once that the Sharepoint connector doesnt allow us to pass a "RelativePath" as parameter, and trying to create a custom function and invoking it from a table containing all the URL's cannot be refreshed on the service (following some topics here I think this is some issue regarding the fact that Power BI Service cannot authenticate the sources once they are variable), my ideia is to work with Web Contents and try to follow Chris Webb's blog post (https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...)

Does anyone know or have any guide on how we can access Sharepoint Lists using Web Contents? I've already tried "converting" the content to XML Table and HTML Table but without success.

Thanks a lot in advance

Cheers,

J. M.

[Gabe_V]

Hi @jennratten and @JOAOLBMARTINS 

I'm in a similar situation. For me I tried to use a function to parse through SharePoint.Tables as follows, putting my root URL and the part that changes inside Web.Contents(), but I haven't had any luck doing that either. I'll keep playing around with other combinations of wrapping Web.Contents and if something works I'll let you know:

(siteURL,listname) =>

let

Source = SharePoint.Tables(Web.Contents("https://projectportal.xyzcorp.com/PWA/", [Query=[siteURL]]), [ApiVersion = 15]),
#"MyListData" = Source{[Title=listname]}[Items]

in

#"MyListData"

[jennratten]

Hello - I have done a lot of work with PowerBI and Sharepoint sources that bypass the dynamic data source issue.  Note, I do not use the Web.Contents function so if you are specifically looking for that then this post won't help.  If you are open to other ideas, here is a link that describes the process.  Note, the linked post is for combining files from various Sharepoint paths but the basic process would be the same to get the data from the lists. 

https://community.powerbi.com/t5/Power-Query/DataFlow-does-not-save-due-to-dynamic-data-source/m-p/2... 

[JOAOLBMARTINS]

Hello @jennratten 

Thanks in advance for the reply.

Unfortunetely I think that your solution won't work in my case.

You're using the Sharepoint.Files() connector, whereas I need to use the Sharepoint.Tables() connector to get the list content.

However, when using the Sharepoint.Tables() I can't find the path to the Subsites from my Sharepoint Root.

I've created a table containing all the subsites's paths and a Function based on a query to one of those subsites.

However, when I "Invoke" the function, I've got the issue with the datasources.