Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bpearce
Advocate I
Advocate I

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
bpearce
Advocate I
Advocate I

Sorry @roselilly23, we implemented work-arounds and have not revisited, so I have no update to share. 

roselilly23
Advocate V
Advocate V

Hi,

 

Has this been resolved yet? I am experiencing the same issue. The problem occurs when I merge data from my excel (connected to via web connector in SharePoint) and the data I have gotten from Sql Server Database.

 

Any help is much appreciated.

 

Thanks

 

Error.pngRose

Anonymous
Not applicable

Dear all, 

 

also we have the problem that we want a folder iteration about a variable number of files. 

 

After that iteration, we merge all excel files in power bi together to get one table.

 

This combination doesnt work with a schedule refresh and doesnt work with on premises data gateway version:

14.16.6549.2

 

Power BI Desktop Version is: Version: 2.52.4921.461 64-bit (November 2017)

Anonymous
Not applicable

Hint.

 

after our merge of all excel files to one table we also want to merge this table with another one. 

In this combination we get the Data Gateway error:

 

" You don't have any gateway installed or configured for the data sources in this dataset. Please install a new personal gateway or configure the data source for an existing data gateway. "

 

 

 

Dear all,

 

I've created a new thread reporting a similar issue: On Premise Data Gateway is not available when merging Odata feeds with (Excel) files on a folder

 

Kr,

 

Evert

jo_mandy
Frequent Visitor

I am still seeing the issue where a merge of an Excel table and SQL table cause Power BI service to not recoginze the configured data sources in the data gateway. My excel source is a file not a folder, that was one of the suggestions but it doesn't make a difference.

 

Removing the merge makes the data sources available so I know that is the problem. The merge is required in my dataset and I cannot work around that. 

 

Did anyone find a different way to resolve this issue? 

 

 

I'm having the same issue with 2 Web.Contents sources (2 API endpoints, set up as 2 different connections). Was able to replicate the issue on gateway ver. 14.16.6549.2. Enabling/Disabling load of a simple Table.NestedJoin query causes refresh to fail/succeed, respectively.

v-qiuyu-msft
Community Support
Community Support

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.

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

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

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.

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

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

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.

 

!! ????

Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi @Anonymous

How do you manage specifying an excel file that is saved in a SharePoint Site. I tried the URL but it is giving me an error. Error shown below:

 

Power BI

Unable to connect: We encountered an error while trying to connect to \\linus\marketing_resources\campfire_reporting\Shared%20Documents\Global%20RFQ%20Chase%20List\DO%20NOT%20USE. Details: "We could not register this data source for all gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details
Activity ID:f567e3d1-423d-4b42-b103-1fd8b1d081e8
Request ID:65445b9a-0081-90b7-3a20-b2ef77075c00
Cluster URI:https://wabi-canada-central-redirect.analysis.windows.net
Status code:400
Error Code:DMTS_PublishDatasourceToClusterErrorCode
Time:Tue Feb 20 2018 08:07:47 GMT-0500 (Eastern Standard Time)
Version:13.0.4385.126
LinamarPBIGateway:Cannot connect to the mashup data source. See error details for more information.
Underlying error code:-2147467259
Underlying error message:The network name cannot be found.
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Microsoft.Data.Mashup.ValueError.Reason:DataSource.Error

 

Thanks in advance.

It is not only ugly but insufficient -- we are merging 100s of files, and the file names change daily.

Anonymous
Not applicable

I'm in a similar boat, although my filenames  don't change.  I feel your pain too.  Its the best i got for you. I'm hotly waiting for this fix too.

So can we all assume Microsoft Gateway folks are aware of the problem & working the issue?

 

Not entirely sure it is related, but I do have another symptom thought I would share. My pbix model which used to refresh via gateway just fine a couple of months back, now behaives as I described earlier (asking for a Gateway to be defined). However, I can still initiate a manual refresh on the dataset in PBI and it will refresh just fine via the gateway.  Weird state. Says it can't find a gateway, but finds it just fine when I click the little fresh icon. Bummer is it will not refresh via schedule refresh. So I set my alarm a little earlier these days so I can initiate a manual refresh... come on Microsoft Gateway team... releave me of this duty please!

Anonymous
Not applicable

It has been reported and acknowledged by Microsoft in other threads, so yes.

 

On to your new symptom.  Was this dataset, with refresh, set up prior to the bug being introduced?  I had at least 1 report that continue to work as long as we didn't touch it.  Both the schedule and manual refreshing continued to function until we went to the "schedule refresh" and made alterations.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors