Forum Discussion
Power BI Service Refresh Failing for Dynamic SharePoint Web.Contents (Works in Desktop)
Hi Purva_2504,
The fact that the query refreshes in Power BI Desktop but fails in the Service suggests the issue may be with how the Service handles dynamically built SharePoint paths, rather than with authentication. Even if TenantRoot and RelativePath are fixed, the Service may still treat the source as dynamic when RelativePath values are assembled from site URLs retrieved during query execution. As a troubleshooting step, hard-code a single project site path in RelativePath and test the refresh in the Service. If that works, it would indicate that dynamic path generation is contributing to the refresh failure.
Thank you.
Hi Purva_2504 ,
Thanks for Your problem.
This is a scenario where dynamic Web.Contents calls to multiple SharePoint site URLs behave differently between Power BI Desktop and the Power BI Service. The root cause is that the Service enforces stricter rules around dynamic data source paths and credential scoping.
Workarounds-
1. Use RelativePath with a Fixed Root
- Keep TenantRoot = "https://mccnetmccneb.sharepoint.com" constant.
- Pass site‑specific segments via RelativePath
This ensures the Service sees one consistent root domain
2. Parameterize Site URLs Safely-
- Store project site URLs in a parameter or table.
- Use RelativePath instead of concatenating full URLs.
- Avoid Web.Contents("https://mccnetmccneb.sharepoint.com/sites/ProjectA/...") directly — that creates multiple roots
3. Enable Allow Data Source to Combine-
- In Power BI Service, go to Dataset Settings - Data Source Credentials - Privacy.
- Set privacy to Organizational and enable combining.
- This reduces blocking when multiple site paths are stitched together
4. Force JSON Output
5. Consider Staging-
If dynamic expansion remains unstable, stage project/task lists into a single SharePoint list or Fabric Lakehouse.
Refresh against that consolidated source instead of looping through sites at runtime.
Microsoft has documented that dynamic Web.Contents across multiple site collections is not fully supported in Service.
The recommended architecture is to normalize site data into one source or use SharePoint Folder connector for consistent refresh.
If this reply helped, a kudos would mean a lot and mark it as a solutions .It encourages contributors and keeps the community strong.
Thank You
Sunita
Hi Sunita,
Thank you for the detailed explanation and suggestions, they were very helpful.
I have implemented the changes you recommended:
- Used a fixed TenantRoot with RelativePath
- Avoided full dynamic URLs
- Set credentials and privacy level to Organizational
- Forced JSON output using $format=json and appropriate headers
In Power BI Desktop, everything works correctly. I am able to retrieve data dynamically from multiple project sites, and I also verified that the API responses are returning JSON and being parsed correctly (d → results) in Power Query.
However, in Power BI Service, I am still encountering the same error:
“The credentials provided for the Web source are invalid.”
Since I am relatively new to Power BI, I’m not sure if there might still be an issue in my query or approach.
From further testing, it seems the query is dynamically expanding across multiple SharePoint site URLs, which might still be treated as a dynamic data source during refresh.
If possible, could you please review or suggest improvements to the query pattern? Also, have you seen any alternative approaches to support a fully dynamic multi-site scenario without using a staging layer?
Thanks again for your guidance!
Hi Purva_2504,
Thank you trivedisunita, for your insights.
Since the query refreshes successfully in Power BI Desktop but fails in the Power BI Service, this could be due to how the Service manages dynamically generated SharePoint site paths, rather than an issue with credentials. Even though you have set a fixed tenant root using Web.Contents and RelativePath, if the site paths are constructed at runtime from the Projects list, the Service might still treat the query as a dynamic data source. You could try hard-coding a single project site path to see if the refresh works in the Service. If it does, this suggests that the dynamic URL generation may be causing the issue. Microsoft’s documentation on dynamic data sources and Web.Contents could provide further guidance.
Data refresh in Power BI - Power BI | Microsoft LearnWeb.Contents - PowerQuery M | Microsoft Learn
Thank you.
