cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 11
Helper II
Helper 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.

 
Helper I
Helper I

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

Frequent Visitor

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

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.

Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi @dthomas,

 

How about the results after checking Privacy Level settings?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Frequent Visitor

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.Capture.PNG

Community Support
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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors