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