Unable to refresh Power BI report that is connected to SQ and Excel

[ruyguy]

I have created a power bi that imports data from a SQL table and an excel file located on a shared drive.  I'm able to successfully publish the report to the Power BI service but when I attempt schedule a refresh I'm getting missing gateway errors.  I have created gateways for both SQL and Excel and i can get the report to refresh correctly if only connected to one of the data source types.  It's only when I use both excel and sql that I am unable to refresh the report.  Any help?

[Anonymous]

Hi there,

I am facing same problem after adding excel file to model. I am unable to setup schedule refresh, for testing I have deleted the file and tried refresh - now its working. What is the best way to overcome this situation.

[Seth_C_Bauer]

@ruyguy Since you placed the Excel file in a cloud location, you can only use a personal gateway. The reason is that mixing cloud and on premises data sources is not supported in the Enterprise Gateway. Based on your description, I'm assuming a few things here, so tell me if I'm wrong.

[ruyguy]

Thanks for the quick response but both the excel files and SQL tables are on-prem.  What is really odd though is that the issue only appears to not work with Excel, I can take the same files and convert them to csv or txt and the refresh works when combined with SQL data,  unfortunately just not with xlsx.

[Seth_C_Bauer]

@ruyguy Hmm. That is odd. You say you created gateways for each source, do you mean you created a datasource for each? Do you have any issues refreshing the data in the Desktop? Are you just pulling the data from the raw tabs in the Excel file?

[ruyguy]

Correct I created a source for each file and for the SQL database.  The refresh process works perfectly with desktop app but once it gets published to the cloud I'm getting the data gateway error.