Merged Queries working with the Data Gateway

[dthomas]

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.

[MaxW]

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.

[KFP]

I had similar issue previously, and it was solved once I updated the gateway. Hope this helps. 

http://community.powerbi.com/t5/Integrations-with-Files-and/Can-t-refresh-data-after-appending-queri...

[dthomas]

Thanks @KFP, I'll try that if I go back to the standard gateway.

[jason435]

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.

[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 Yu

[dthomas]

Yes, merged query does work great in Power BI Desktop.  I'll look into the privacy level settings.

[v-qiuyu-msft]

Hi @dthomas,

How about the results after checking Privacy Level settings?

Best Regards,
Qiuyun Yu

[dthomas]

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.

[dthomas]

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,

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_moksh]

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?