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

[Yggdrasill]

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)

let
SharePointURL = "https://MyCompany.com/sites/MySharePointSite/",
    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]
in
    MyExcelTable_Table

With this code I get my table easily in Power BI Desktop using OAuth on prompt and I choose Organization in Privacy Settings.When 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 cloud.

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

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.

[v-xicai]

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

Amy

[Yggdrasill]

Hi Amy, thanks for taking the time but your proposed solution doesn't work.

[v-xicai]

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,

Amy

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