Forum Discussion
SharePoint Sites Authentication as part of Web.Contents(_,[RelativePath=_])
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.
Resolver IIII'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.
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.