Gateway required for refresh of excel file on Sharepoint Online?

[Anonymous]

I have an Excel file on Sharepoint Online.

The excel file has a simple power query to get data from web from this simple URL: http://www.contextures.com/xlSampleData01.html

(this was just a sample to prove it works/doesnt work - the website does NOT need a password) 

I load the data from the website to the worksheet and add to the data model. Everything fine.

I connect to the Excel file in Power BI service, load the data the data to the dataset and the data is loaded fine.

I go to schedule a refresh and I am met with an error "You don't have any gateway installed or configured for the data sources in this dataset. Please install a new personal gateway or configure the data source for an existing data gateway."

and another:

"Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."

Why do should I need a Gateway installed for a file that is stored and read (sucessfully) from sharepoint online? 

I have had other Power Query data read from an Excel file into Power BI Service without issue (they werent web querys). So not sure what the problem is with web queries in power query.

Please dont tell me to use Power BI Desktop.  Everything I have done is built around Power BI Service. I dont want to install a gateway as it would mean my machine would have to be switched on all the time. 

Thanks

Ger

[igorzaritsky]

What about PowerBI Server connecting to an excel file in Sharepoint online?

I am able to refresh the Power BI model in desktop, but when I publish it to server, I only have two options for authentication Windows and Anonymous and both do not work. should I see an option for Oauth2?

My question is: How do schedule a Power BI Report refresh on server  to an excel file in Sharepoint online when i try to create a schedule refresh, I get prompted to update the datasource credentials. When I try that Windows credentials dont work on server, but they do work on Power BI Desktop?

[Anonymous]

Why would an excel file stored on sharepoint online with a simple power query web query require a personal gateway for refresh in Power BI Service?

Also asked here

It makes no sense, and my understanding of how this should work from this site  indicates that a gateway is not required. None of the data is on premise (neither the excel file, nor the source for the query), so I dont know why I need a gateway.

Thanks in advance for any assistance 

[Seth_C_Bauer · Accepted solution]

@Anonymous same answer to your other post:

It most likely has to deal with the connection to the web datasource. If you see here and here, there was a change in late November that required a gateway for connecting to web datasources in most instances. Most likely a security issue.

[Anonymous]

First of all, thank you so much for replying to me... I really appreciate the support. As with most new technology, your never sure if it is something you have done wrong, or if it is by design. The answer was not what I was hoping for, but at least I know its nothing I was doing wrong. The link you pointed to here actually answers the problem.

[quote]

Online data sources

A gateway is only required if you are using the Web.Page function. In other cases, a gateway is not required in order to refresh datasets that get data only from an online data source.

[/quote]

My Excel file contains a power query which uses "web.page" to get its data from the URL... hence the need for the personal gateway. 

I had gone ahead yesterday anyway and installed the gateway (pretty painless to be honest) and it is working and refreshing just fine. Although it is a little bit of a pain to have the laptop on for the refresh to happen. Thats not a deal breaker though. 

Again, thanks for confirming and taking the time to respond to me. It means a lot. 

Best Regards

Ger

[Seth_C_Bauer]

@Anonymous My pleasure, I'm glad that the linked helped and you were able to figure it out. Welcome to the Community 🙂

[Seth_C_Bauer]

@Anonymous It most likely has to deal with the connection to the web datasource. If you see here and here, there was a change in late November that required a gateway for connecting to web datasources in most instances. Most likely a security issue.