Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Gateway required for refresh of excel file on Sharepoint Online?

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. Smiley Frustrated 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

1 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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

6 REPLIES 6
igorzaritsky
New Member

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
Not applicable

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.

 

Gateway.png

 

Thanks in advance for any assistance Smiley Very Happy

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

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

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors