Showing results for 
Search instead for 
Did you mean: 
Yggdrasill Member

SharePoint.Contents() and Power BI ON PREM GATEWAY NOT WORKING

I've gone through countless of "solved" threads on this forum but somehow NONE of them work.


I'm hereby going to list out all the testing I've been doing in order to get to the bottom of WHY I can't schedule a refresh in PBI Service.

I'm connecting to an Excel file stored on Sharepoint like so (Note, I've also tried everything using SharePoint.Files() function)

SharePointURL = "",
    Source = SharePoint.Contents(SharePointURL, [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    General = #"Shared Documents"{[Name="General"]}[Content],
    Sources = General{[Name="Sources"]}[Content],
    #"MyExcelFile" = Sources{[Name="Fiskistofa_MyExcelTable.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"MyExcelFile"),
    MyExceTable_Table = #"Imported Excel"{[Item="MyExcelTable",Kind="Table"]}[Data]

With this code I get my table easily in Power BI Desktop using OAuth on prompt and I choose Organization in Privacy Settings.2019-09-16 17_08_14-Edit Permissions.pngWhen I publish the file to PBI Service I'm notified that I don't need to use a gateway since my source is in the


In another section I'm offered the option to enter credentials in the service, so I do that EXACTLY like in the PBI file



I use OAuth with Organizational Privacy Settings and It seems to work.


However when I manually refresh in the cloud I always get this error

2019-09-16 17_18_17-Power BI.png

I've also tried using the On prem data gateway and everything seems to be working fine until I hit that Refresh Now button.
I iterate, this works perfectly in PBI Desktop.

Anyone else with the same dilemma ? I suspect so.

Community Support Team
Community Support Team

Re: SharePoint.Contents() and Power BI ON PREM GATEWAY NOT WORKING

Hi @Yggdrasill ,


Please check the usernames and passwords that you used to sign in to data sources aren't up to date. In Power BI, go to refresh settings for the dataset. In Manage Data Sources, select Edit credentials to update the credentials for the data source.


You may try to upgrade the Power BI Desktop to the latest version, then republish the original pbix again. See the similar case.


If your dataset not only contains SharePoint on line data source but also the on premise data source, then it needs to use the gateway and configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .


Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.




If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.


Best Regards,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team
Community Support Team

Re: SharePoint.Contents() and Power BI ON PREM GATEWAY NOT WORKING

Hi @Yggdrasill  ,


Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.


Best regards


Helpful resources

Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 314 members 3,270 guests