SharePoint Sites Authentication as part of Web.Contents(_,[RelativePath=_])

[CWPRenewables]

I have a small dataset of about 6 workbooks that are stored across various places in a large SharePoint site.

I can't use a gateway and the Folder connector, because the file paths are too many characters, and I can't move the files.

Using the SharePoint.Files connector works, but the report takes between 1-2hrs to refresh about 1,000 rows of data.

Using SharePoint.Contents doesn't help in this case, as the files are spread throughout too many different folder paths.

This got me thinking about Chris Webb's Web.Contents RelativePath solution.

While I can build a Dynamic Data Source that refreshes in Desktop in about 1 minute using the Web.Contents approach, I can't seem to take advantage of the RelativePath option because I can't authenticate into the subsite.

my code:

Desktop Solution works a charm:

=Excel.Workbook(Web.Contents(Root&Path&File), null, true)

Root, Path, File are obviusly Parameters, and then I use a function to call the correct details.

So, I attempted to modify this approach for the service:

=Excel.Workbook(Web.Contents("MyCompany.SharePoint.com/sites/SubSite", [RelativePath=Path&File]), null, true)

In theory I thought this should work, because if i type "MyCompany.SharePoint.com/sites/SubSite" into my browser, I can sign in and all is good.

BUT, in Power Query, I have to add a suffix to the URL before I can successfully authenticate:

"MyCompany.SharePoint.com/sites/SubSite/SitePages/Home(1).aspx"

This Suffix obviously messes with my ability to use RelativePath.

Does anyone know why I can't authenticate into "MyCompany.SharePoint.com/sites/SubSite/" by itself?

Any ideas how I could overcome this?

[CWPRenewables · Accepted solution]

Overcoming the challenge of Power BI Service preventing Dynamic Data Source refreshing, it would appear primolee had a very similar problem. 

The solution mentioned there was to set up the source file paths as parameters.

This solution works, and only requires a small amount of maintenance on the report:

1. if the source files move to a different location, or change their name, the parameter for that file must be updated.

2. if a new source file is created, we can't rely on it being automagically added to the report.  Instead, a new query must be created, with its own new parameter.

Therefore this solution works well for a small number of files on a large SharePoint drive.

Where my report previously took over an hour to refresh in Desktop, it now takes less than a minute, and the report is able to run scheduled refreshes on the service which are fully refreshed in under a minute also.

All in all this is a viable solution for my specific problem, but it would obviously be a large overhead for reports relying on many more data sources, or situations with a quickly growing number of data sources.

[MisterFry]

I'm confused by your description of the issue. 

When I connect to sharepoint (via the sharepoint folders connection type) and connect to MyCompany.SharePoint.com/sites/SubSite/, I get a list of files, and the path is a field in that table. So, if I know exactly the name of my file, the path is irrelevant. Can you not locate the files you need to use without addressing the file path column in that list? 

Related to your 1-2 hr. refresh time, try updating your power BI to the latest version. I had a version that was really non-performant when connecting to Sharepoint, and updating my version fixed it. 

[CWPRenewables]

Thanks MrFry,
Unfortunately, the SharePoint.Files (via the SharePoint Folders connector) was my original approach, but that causes the issue with the long refresh times that sometimes fail with the api/contextinfo error.

WRT refresh time, I am running March 2021, so it's not that, i think it is just because we have so many files in SharePoint (300,000 files), and the SharePoint.Files connection loads all of the files on the wite before it filters for the 6 I want to keep.