how to appened multiple server query and implement incremental refresh in power bi

Hi shafiz_rn,

I’ve run into this challenge before when dealing with multiple servers behind different gateways. The tricky part is that incremental refresh only works cleanly when query folding is preserved, and once you append queries from different sources, folding usually breaks. That’s why you’re not finding a “native” way to do it in one master query.

The more reliable approach is to set up incremental refresh separately on each source query. Each server will then only refresh the new/changed data, which keeps things efficient. After that, you can combine the results at the model level (using DAX or composite models) rather than trying to union everything first. It’s generally more scalable than forcing one giant append.

If you want a cleaner architecture, consider staging the data in dataflows (or Fabric Lakehouse if you have access). Each server pushes into its own dataflow with incremental refresh, and then you append those dataflows into a master dataset. That way the heavy lifting happens upstream, and your report only deals with already‑optimized tables.

On refresh times: with 200K–400K rows per month, incremental refresh should help a lot, but only if the filter logic folds back to the source. If folding breaks, Power BI will pull everything and filter locally, which is slow. So the key is to keep folding intact per source.

Since you’re on PPU, you can definitely use incremental refresh, but I’d recommend doing it per source (or via dataflows) rather than trying to append first. For cross‑region reporting, combining at the semantic layer with DAX is fine and usually more efficient.

Hi shafiz_rn 

it is possible and manageable if done properly.By Server i assume you are talking about different environments

follow below steps to make sure incremental refresh work properly.

1. Create Parameters to store All Server,database and schema.
2. Create a UNION ALL query combining all server queries.
3. use the query in PowerQuery and parameterize the query using parameters created in step 1.
4. Make sure query is folding after step3.
5. Create RangeStart and RangeEnd Parameters and apply filter on date column.
6. Configure Incremental refresh policy.

If your Servers are CLoud based then there is no need of gateway for them.

if the setup is configured correctly the first refresh will take time as it will create and load all partitions.but subsequent refreshes will be faster.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

shafiz_rn Two constraints in your post decide most of this, and they are worth separating before you commit to an architecture.

The first is the gateways, and it is the harder one. The gateway documentation is direct: "If your dataset contains multiple data sources, add each one to the gateway. If a data source isn't added, you don't see the gateway as available for scheduled refresh." The practical consequence is that whichever gateway you pick has to be able to reach every on-premises source in that model. Appending the queries in Power Query does not change that, because the append happens after the data has been fetched, and each fetch still has to go through a gateway that can see that server. Clustering does not rescue it either. A cluster is for availability, any member can take a request, so a member that cannot reach one of your servers simply fails when a request for it lands there.

The second is the licence, and it rules out half of the advice above. You said PPU only. Premium Per User does not provision a Fabric capacity and does not let you create or run non-Power BI Fabric items such as lakehouses, warehouses or notebooks, so the Lakehouse route is not open to you. Dataflows are, and this is one of the few places where Microsoft's own guidance names your exact licence: for a shared source it recommends a Dataflow Gen2, "or a dataflow (legacy) for Pro/PPU users without Fabric access".

So the shape I would build is one Gen1 dataflow per location, each in a Premium workspace, each refreshing through its own local gateway, each with its own incremental refresh. Then a single semantic model that reads those dataflows and appends them there. At that point the append is between cloud sources and the gateway problem disappears completely, and each region refreshes independently instead of one unreachable server taking down the whole load. Two documented details that make it work on your licence: a Pro or PPU user can create a dataflow in a Premium workspace, and dataflow incremental refresh is a Premium feature, which a PPU workspace satisfies.

On your direct questions.

Incremental refresh per query is not slower, it is the reason the thing gets fast. Only the partitions inside the refresh window are queried, so a one month window over 200K to 400K rows is a small read no matter how many years of history you are storing. The one that genuinely hurts is the very first refresh, which loads the entire store period in one go.

DAX at the model level for the cross-region reporting is fine, and it is not where your cost is. Combining at the model rather than in Power Query is the normal answer whenever sources cannot be folded together anyway.

One trap to check on every single branch before you publish, because it produces duplicate rows quietly instead of an error: "Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions." With one query per server that is a check you have to do several times over, and each one is its own opportunity to get it wrong.

Before any of that though: are those servers genuinely unreachable from one machine, or did each location just install its own gateway because that was the path of least resistance at the time? If one gateway can see all of them, most of this problem goes away.

Thankyou Praful_Potphode​ , PauReis​ , ShahRukhSameer​ for Addressing the issue.

Hi shafiz_rn​   ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya