Data Gateway does not allow merges of tables coming from network drive

[bdymit]

We are having issues with our On-premises data gateway. For some of our data sources, we have .csv files in a network folder. Let's call it \\abc-123\reports\DashboardSources\

We have multiple folders within this main folder that each contain one .csv file. These files get overwritten each night with new data from the previous day.

Example:

\\abc-123\reports\DashboardSources\folder1\file1.csv

\\abc-123\reports\DashboardSources\folder2\file2.csv

We set up a On-Premises Data Gateway to connect to the main folder, "\\abc-123\reports\DashboardSources\"

In one report, we have file1 merging with file2 in Power Query. This gives us the following error:

The issue is not, though, that the gateway does not exist. If we remove the step in the query where we merge the tables it works fine. Conversely, if we set up gateways to each individual file (file1 and file 2) instead of using one gateway to the main folder, it also works.

What makes this even more confusing is that we set this dataset up yesterday to refresh with no issues. It refreshed 6 times overnight with no problems. When we got to work this morning to add other datasets, this one could no longer connect via the data gateway. 

@dimazaid can anyone on the gateway and connectors team help out?

Thanks!

[v-shex-msft]

HI @bdymit,

For on-premise gateway, it not support to auto analysis child folder and files, please add the match type data sources. (add every files as the data sources of gateway instead the main folder)

If your report use the combine datasource, power bi will try to use personal gateway to manage your datasource. (For gateway personal mode, it not need to consider the source setting)

Reference:

Power BI Gateway - Personal

Regards,

Xiaoxin Sheng

[Anonymous]

I'm having a similar issue to what is being described here.  Merge operation on tables causes the gateway to no longer recognise the project as being valid for refreshing.

Adding link to my thread just in case there are details in my situation that assist solving this issue.

https://community.powerbi.com/t5/Integrations-with-Files-and/Strange-Gateway-issue/m-p/269883#M13296

[bdymit · Accepted solution]

For anyone who found this thread, refer to this one.

https://community.powerbi.com/t5/Issues/Gateway-disabled/idc-p/277680#M15906

Microsoft is taking action there.

Thanks!

Ben

[bdymit]

We also have some sources that are tables in a sql server. If we try and merge two of those tables together in Power Query, we do not get the same issue.

So merging two tables is OK if it comes from a sql server (just one Data Gateway to the server), but it does not work to merge two files coming from the same folder (again, using one Data Gateway)?

[bdymit]

Additionally, it seems as though we can set up the file to schedule refresh when there no merges in the query, but then add merges to the query and "overwrite" the dataset. It seems to keep the refresh settings, and it successfully refreshes. If we try to set up that new file with the merges to refresh, though, it will not allow us to do so, giving the error message above.