cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dthomas
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
MaxW
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.

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

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

v-qiuyu-msft
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.

dthomas
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

v-qiuyu-msft
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors