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

Data Gateway - won't allow merge of tables from different sources

In Power BI Desktop I have a Power Query that pulls LocationDim from SQL Server and another Query that pulls a list of locations from an Excel file. Both queries load to the data model. I can publish this model & have both tables refresh via an On Premise Data Gateway. However, if I modify the LocationDim query & perform a Merge function (inner join to filter LocationDim by the locations in the Excel list), this model cannot be refreshed by the Data Gateway. Instead on the Power BI / Refresh Schedule page it tells me that I need to install a Data Gateway for the data sources specified in the model. 

 

Does the Data Gateway not support mixing data from two different sources defined in the Gateway?

31 REPLIES 31
Moderator v-qiuyu-msft
Moderator

Re: Data Gateway - won't allow merge of tables from different sources

Hi @bpearce,

 

In your scenario, as the dataset contains SQL database and Excel data sources, to use the on-premise data gateway, those two data sources need to be created under data gateway. From your description, it seems after you combine those two tables, then refresh is failed. Can you share the detail error message? 

 

Please check Privacy Level in Power BI desktop and on-premise data gateway are all set as Organizational or Public. See: Power BI Desktop privacy levels

 

large (6).png

 

e7.PNG

 

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.
bpearce Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

Thanks for the response. 

 

I verified that both the SQL Server & Excel sources have a privacy setting of "Organization" as defined in the Gateway.

 

Below is the error message I get when I try to perform a merge function in Power Query. If I remove the merge function & republish this error goes away and Power BI is able to refresh the model via the Gateway.

 

GatewayError.PNG

vgdits Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

Hi!

 

We experience exactly the same issue. Without any modification on our PBIX, as from Wednesday suddenly PowerBI web says there is no installed gateway. As soon as we remove merge of SQL data to Excel data, the gateway is "back on". Privacy organisational. Each source separately works fine, and also works fine in single PBIX until merged. Merge step kills it.

Enterprise gateway, error message is identical to what bpearce screenshotted already.

Tried to clear permissions, but actually the issue is reproducable even if we start from blank PBIX, and just simply add two sources and merge them.

 

Any ideas?

 

Jiri

vgdits Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

Hi!

 

We experience exactly the same issue. Without any modification on our PBIX, as from Wednesday suddenly PowerBI web says there is no installed gateway. As soon as we remove merge of SQL data to Excel data, the gateway is "back on". Privacy organisational. Each source separately works fine, and also works fine in single PBIX until merged. Merge step kills it.

Enterprise gateway, error message is identical to what bpearce screenshotted already.

Tried to clear permissions, but actually the issue is reproducable even if we start from blank PBIX, and just simply add two sources and merge them.

 

Any ideas?

 

Jiri

Moderator v-qiuyu-msft
Moderator

Re: Data Gateway - won't allow merge of tables from different sources

Hi @bpearce@vgdits

 

I'm not able to reproduce this issue on my side. Please try to update the Power BI desktop to the latest October version then republish again. 

 

If issue persists, please run the Fiddler with Decrypt HTTPS traffics enabled, repeat the steps to publish report and go to dataset settings gateway connections, save all sessions as .saz file and share it with us. Also share the on-premise data gateway service log (C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log). 

 

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.
pgerula Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

I have the same issue.

When I publish new pbx which doesn't contains merged or append queries I can set on-premises data gateway but if I add at the same pbx append queries than I can't set data gateway except personal datagateway. It's after update data gateway to version 14.16.6464.1 in October.

 

Best Regards,

Pawel Gerula

vsagan Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

Hi,

 

We have been able to easily replicate this error with multiple users at our organisation, with the following steps:

 

Query1:  Source = Folder.Files("some UNC folder path")

Query2:  Source = Folder.Files("Some other UNC folder path")

 

Publish -- add folders to enterprise gateway -- refresh works with gateway

 

Then, add (append command):
Query3:  Table.Combine({Query1, Query2})

 

OR add (merge command):

Query3:  Table.NestedJoin({ relevant stuff})

 

Publish -- gateway fails with the errors described above.

vsagan Frequent Visitor
Frequent Visitor

Re: Data Gateway - won't allow merge of tables from different sources

Hi,

 

I would also like to mention that we have a PBI desktop workbook which appends/merges as described above, and this file was published many weeks ago.   It had one day of failed updates on XXX.  Now it is refreshing with a schedule through the gateway just fine.

 

However, if I use the PBi Service feature to download the report (pbix) file, then open this file in desktop and publish to a new workspace, scheduled refresh does not work with the errors described above.

 

!! ????

Super User
Super User

Re: Data Gateway - won't allow merge of tables from different sources

There is currently a known issue with Gateways and merging if you are using "Folder" in your gateway.

 

Have a look at your data sources in your Manage Gateway.  Rather than list the folder your Excel file is sitting in, list the file specificly.  Its ugly, but its a work around until the problem is resolved.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!