This dataset includes a dynamic data source. using Sharepoint.Tables

[MrDeg]

Hi,

I created a Report that extract data from multiple Sharepoint sites and Lists. In order to avoid to create manually a quantity of tables to append, I created a function that loop over some tables (each for type of List) where each row has all the infos required to connect with the required Sharepoint adress, subsite and list name etc.  

everything worked fine until I published the report and I realized that I cannot schedule the refresh.

this is the function:

= (SharepointLink_S as text,SubSiteName_S as text , TableTitle_S as text) =>
let
    //Raw Data
    Source = SharePoint.Tables(SharepointLink_S & SubSiteName_S, [Implementation="2.0", ViewMode="Default"]),
    MyTable = Source{[Title=TableTitle_S]}[Items]

in
   MyTable

the tables is something like this one, then I create a Custom colum where I put the function above.

SharePoint link	Sub site name	List Name
https://MyOrganization.sharepoint.com/teams/a952ea75/	SubSiteN1	ListNameN1
https://MyOrganization.sharepoint.com/teams/a912ea75/	SubSiteN2	ListNameN2

What I understand is since the link to the sharepoint is generated by M language, it is not possible to evaluate the connection before the script starts. did I get it right? What I ask you is: is there a way around this limit or do I have to completely review my strategy?

thank you in advance for your support

[lbendlin]

You are mixing up terminology. 

A site is identified by the value after /teams/ or /sites/ and is the starting point of the connector.

A subsite is being tacked on to the site and cannot be directly connected to. 

A list is a piece of content in a site.

You may need to precreate all site connections.

[MrDeg]

thank you for you fast reply.

Yes sure, I'm completely aware of what is a Site, Subsite and list.

Just to be sure that I'm understanding correctly:

When you say "You may need to precreate all site connections" what do you mean exactly?

the only way I know to create a connection is to create a table for each single list instance, and this is exactly what i tried to avoid with the function above. 

thank you in advance for your patience.

[MrDeg]

Thank you for your reply.

I know All the Sharepoint names; to be more specific, the SharePoint site is the same, but sub-site are different, I have several istances of the same SharePoint lists stored in several SharePoint subsites in the same site.

my Sharepoint structure (simplified)

Now, what do you mean exactly with "precreate the connectors"? I tried to create a table that extract data from the "father" SharePoint. But this not solve the problem. Do I have to to so for each single list?

[lbendlin]

Unfortunately yes.  Unlike SQL server where (at least on Desktop)  you can connect to SQL.Databases() the sharepoint connector only works with complete Site URLs.

Now, if you know the site names for ALL of your sites then you can precreate the connectors for all of them, and still get away with your script.