Dataflow table to Linked Dataflow Table Mixed On-prem Sources

[JaromBIDEVatDK]

Hi there,

I am trying to connect two dataflows together. Dataflow 1 has an on-prem gateway query to sql. Dataflow 2 has a table that I want to bring into dataflow 1. When I bring the table into dataflow 1 it asks me to select a gateway. Since dataflow 2 table is online, I select "none" as the gateway and can make a linked connection. However, when I change the gateway to accomodate the dataflow to none, it changes all the gateways on the dataflow to none. So the dataflow 1 sql query won't work. Does anyone know if what I am doing is a limitation to dataflows/i.e. I can't do it? Or is there a workaround? Just FYI, my gateway is setup to allow mixed onprem and cloud. 

Thanks in advance! 

[bcdobbs]

Hi,

My understanding is that either everything has to refresh through a gateway or nothing. That's certainly how it works if you do the power query directly in a dataset.

I think the reason being that if do any joins it mashes the data up in the gateway itself.

[bcdobbs · Accepted solution]

If you don't want the cloud source to refresh through the gateway the alternative and I think recommended way is to create a dataflow that ingests the SQL data. Then in another dataflow merge the two dataflows. Effectively 2 dataflows at that ingest and a 3rd that does the transform.