Forum Discussion
Merged Queries working with the Data Gateway
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.
11 Replies
MaxWAdvocate II
Hi,
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.- v-qiuyu-msft
Community Support
Hi dthomas,
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?
Best Regards,
Qiuyun Yu
nirvana_mokshImpactful Individual
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?
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.
- v-qiuyu-msft
Hi dthomas,
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.
Best Regards,
Qiuyun YuYes, merged query does work great in Power BI Desktop. I'll look into the privacy level settings.
- v-qiuyu-msft
KFPHelper I
I had similar issue previously, and it was solved once I updated the gateway. Hope this helps.
jason435Helper II
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.
