Data refresh from Excel Online file in OneDrive failing

[mwaters4]

Hey all... having trouble with something and need some assistance, please... 

I built a pbix file in import mode.

It has a link to a table on an Excel Online file in our O365 portal and a link to a SQL Server database view on our local server.

We have a gateway setup to refresh that SQL Server data.

It appears that the Excel Online file is not refreshing any more for some reason.

A. Can I have a pbix file that has both an Excel Online file connection via web url and a Sql Server connection that uses a gateway to refresh and have both of them refresh?  

B. If A. is true... why is my Excel Online file not refreshing?  I do get a message about credentials missing on one data source and it appears to be the web url data source to this Excel Online file, but it also says that credentials are not required... so im confused.

Please help!?  Thanks in advance!

[mwaters4]

So I created a brand new pbix file with no data source except for the Excel Online file and published it to the Workspace for testing...

I appear to be having the same credentials issue as on the other file.

My username absolutely has access to that file b/c im currently in it right now editing the Excel Online file, so Im not sure why the creds are not working in power bi service to refresh... 

Ive tried every Basic Authentication method and none worked, so I switched to OAuth2... that allowed the connection to work!

So... not sure why I had to to change that from how it originally was connected in the pbix file in power bi desktop app... thoughts?

Also... after I changed that, im still not seeing the data refresh automatically just yet in the service.

I recall doing this weeks ago with a file and I could make a change in the OneDrive Excel Online file and almost instantly the Power BI Service report/dataset would update with those changes.  Im not seeing that happen here... 

Anyone know how long it takes for a OneDrive file to refresh its content over to a power bi service dataset/report?

thanks

[mwaters4]

ive waited for an hour and a half on the second test dataset that only has the Excel Online Onedrive file loaded to it.... it will not refresh automatically!

Anyone have thoughts on this?

Do I have to turn on a gateway or setup a scheduled refresh...?  I thought the whole point of using the Excel Online file in OneDrive connected to power bi service was so it would stay in sync... confused...

thanks

[Anonymous · Accepted solution]

HI @mwaters4,

According to your snapshot, it seems like you are work with report that uses mixed data source(both on-premise and online datasource).
If this is a case, please check on below document to turn on the option that allow the gateway to manage cloud datasource: (online Datasource not require a gateway to handle refresh operations)
Merge or append on-premises and cloud data sources 

Reference link about data refresh in power bi:

Data refresh in Power BI 

Regards,

Xiaoxin Sheng

[mwaters4]

thanks... wish that werent the case, but I understand...