Refresh fails for Excel workbook on OneDrive

[Reza]

Hi everyone,

I have an excel workbook that connected to an Azure database. It is working fine and refresh works. 

I then uploaded this to the OneDrive-Business of one of our account with free PowerBI services. Then in My Workspace in PowerBI I created a workbook pointing to the OneDrive excel workbook. 

On the Schedule Refresh I updated my credentials to Basic and used the username/password to connect to the Azure database.

But when I click on Manual refresh it fails. I can confirm that the refresh works from the workbook itself when I perform a refresh all.

I cannot get anything from the error details:

Last refresh failed: Thu Feb 01 2018 02:36:09 GMT-0800 (Pacific Standard Time)
We couldn't connect to the data source. Check the connections settings in the data gateway.

Failed Connections:	MyWorkbook
Cluster URI:	WABI-UK-SOUTH-redirect.analysis.windows.net
Activity ID:	c315678b-56e8-7eba-0baa-9b26d6a52942
Request ID:	05b89296-d348-d1dc-a38c-b8d8101c87e4
Time:	2018-02-01 10:36:09Z

Does anyone had this issue or know how to fix that?

Thanks

[v-yulgu-msft]

Hi @Reza,

How did you get data from the Excel file on OneDrive for business?

 

In my test, if I choose "Connect", there is no need configure data gateway for workbook refresh. And I can manually refresh the workbook successfully. As mentioned in this document, gateway is not required in case that "Power Query* is used to connect to and query data from any listed online data source and load data into the Excel data model".

If I choose "Import", then, a new dataset is created rather than a workbook. To enable scheduled refresh, I only need to turn on the "OneDrive refresh" option.

Best regards,

Yuliana Gu

[Reza]

Hi @v-yulgu-msft,

Thanks for the reply.

I chose Connect and I deleted and added again in case I have done any mistake.

So when I go to schedule I see the credentials not set. And as you can see the Gateway is not set.

I then set the credentials to Basic with correct username/password of my Azure database. 

 Then perform manual refresh. And back to schedule I can see the error

If I select Import which creates Dataset then refresh works.

That's where I am struggling to understand what is the difference!

Thanks