Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Sorry @roselilly23, we implemented work-arounds and have not revisited, so I have no update to share.
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
Rose
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)
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
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.
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.
Best Regards,
Qiuyun Yu
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.
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
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.
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.
!! ????
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.
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
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.
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!
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.