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 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 Learn
Web.Contents - PowerQuery M | Microsoft Learn
Thank you.