I have 2 queries, one is an Excel file one is a SQL ODBC query. I can publish both easiliy and the data gateway can refresh both.
However, if I merge them in Power BI Desktop to create a mergerd query, then publish to power BI, the data gateway will not refresh.
I realise this is an old thread but it's the one I found when searching for merging queries between gateway and cloud (SQL Server and Salesforce in my case) and it doesn't have the solution that worked for me. I had to go into the gateway cluster settings in the PBI web service and tick the box to allow cloud data sources to refresh through the gateway.
I had similar issue previously, and it was solved once I updated the gateway. Hope this helps.
I am having a similar problem to this.
I have and ODBC database (PostgreSQL), and a couple tables in excel. The excel files are in onedrive. I would like to 'merge queries' a table from ODBC and an excel table. It works fine in PowerBI desktop. However, I can not get the gateway to work once I 'expand' the columns after the table merge.
I can get the gateway to work if I delete the merge. I have datagateway enterprise. I could switch to personal for testing, but in the long term I would need to get enterprise working.
Can you refresh the merged query successfully in Power BI Desktop? Also please check the Privacy Level Settings both in desktop and data sources under on-premise data gateway.
How about the results after checking Privacy Level settings?
It didn't, but I changed to a personal gateway and that works. So, I've been happy with it working on the personal gateway and haven't tried yet going back.
Hi, thanks for taking time to look at this. Here is the error I get-
Again, I can create a query from either the file or the ODBC connection alone, and the on-premise data gateway refreshes with no issues. But as soon as I create a merged query using both sources, I cannot get it to refresh. I beleive my gateway data sources are set up properly since I can get them to work fine individually. I have done a lot of trial and error testing and just can't get it to work.
I'm not able to reproduce the issue on my side. In Query Editor, I use the Merge Queries as New, then publish the report to Service, I can also manually refresh the dataset use on-premise data gateway.
In your scenario, what do you mean "the data gateway will not refresh", is there any error throws out?
I am facing the same issue, I am mergeing Excel Online data with On-Prem SQL data and to over come this issue I had to use Personal Mode Gateway for the refreshes. This is good but the Personal Mode Gateway has limitations when compared to Enterprise Gateway. This is a bug that needs to be fixed. Is there a way I can use Enterprise Gateway with a merged query?
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.