Incremental Refresh and Real-Time Data in Power BI - A Practical Guide

There is a moment a lot of Power BI projects hit around the two-year mark. The dashboard everyone loves starts taking longer and longer to refresh. What used to finish in five minutes now runs for forty, then it starts timing out, then someone gets a 2am alert because the overnight refresh failed and the sales team walked into stale numbers. The usual reaction is to throw more capacity at it. Sometimes that helps for a quarter. Then you are back in the same spot, just paying more for the privilege.

Almost always the real fix is incremental refresh, and almost always nobody turned it on because the model worked fine when it was small and nobody went back to fix the thing that was quietly becoming a problem. So this is the field guide to incremental refresh, plus the related question everyone eventually asks: what if I do not want to refresh at all, and I just want the live number? Microsoft's incremental refresh overview is the reference doc. This is what it looks like in practice.

Why full refresh stops scaling

By default, when Power BI refreshes a semantic model in import mode, it throws away everything and reloads the whole table from source. Every single day. If your sales table has seven years of history and forty million rows, Power BI is re-querying and re-importing all forty million rows every night, even though only yesterday's rows actually changed. The other 39.98 million rows are identical to what they were the day before. You are doing enormous amounts of work to load data that has not moved.

That is fine at ten thousand rows. It is painful at ten million. It is a genuine problem at a hundred million, and by then your refresh window is blown, your source database is groaning under the query load, and your gateway is the bottleneck for the whole environment.

Incremental refresh changes the deal. You tell Power BI to partition the table by date and only refresh the recent partitions. Historical data that will never change again gets loaded once and then left alone. So instead of reloading seven years every night, you reload the last ten days and leave the other six-and-a-half years exactly where they are. The refresh that took forty minutes now takes two. The load on your source database drops by ninety-something percent. Nobody gets the 2am alert.

How you actually set it up

The mechanism is two parameters with names Power BI treats as special: RangeStart and RangeEnd. You create them as date/time parameters in Power Query, then filter your table so it only keeps rows where the date column falls between them. In development those parameters hold a small window so you are not dragging the full history onto your laptop. When you publish and refresh in the service, Power BI ignores your dev values and swaps in its own ranges to build and refresh each partition.

Then you define the policy on the table. Two numbers do most of the work. First, how far back to store data: "archive data starting five years before refresh date". Second, how far back to actually reload each time: "refresh rows in the last ten days". Everything older than the refresh window is frozen. Everything inside it gets reloaded so that late-arriving data, corrections, and updates to recent records get picked up.

That refresh window is the knob most people get wrong. Set it to one day and you refresh fast, but if a record from last week gets amended in your source system, Power BI never sees it because last week is outside the window and frozen. Set it to ninety days and you are back to slow refreshes, just less slow than a full reload. The right window is however far back your source data realistically still changes. For a lot of finance systems that is the current month plus a few days for the prior-month close. For an operational system where nothing is ever backdated, two or three days is plenty. You have to actually know your data here, and this is exactly the kind of thing that gets guessed at when a model is built in a hurry and never revisited.

There is a nice extra: detect data changes. Instead of blindly reloading the whole refresh window every time, you can point Power BI at a "last modified" column, and it only reloads a partition if something in it actually changed. If your source tracks modification timestamps properly this cuts the work down again. If it does not track them reliably, skip this, because a source that lies about when rows changed will quietly serve you stale data and you will trust it.

The catch nobody mentions until it bites

Incremental refresh needs query folding. Power Query has to be able to push your RangeStart and RangeEnd filter all the way down to the source as a WHERE clause on the database. If it can folds, the source returns only the rows in the partition and everything is fast. If it does not fold, Power BI pulls the entire table into memory and filters it locally, which defeats the whole point and is somehow slower than not having incremental refresh at all.

Folding works cleanly against SQL Server, Azure SQL, Synapse, and most proper databases. It falls apart the moment you do something Power Query cannot translate to SQL, and a lot of the transformations people add without thinking break folding: certain custom columns, some merges, anything that touches a non-foldable connector. The frustrating part is there is no error. It just silently runs slowly. We have been called in more than once to look at an incremental refresh that "isn't working" and found the culprit was one added step near the top of the query that broke folding for everything below it. If you set this up, verify folding on the filter step in Power Query before you trust it. Right-click the step, check that "View Native Query" is available, and confirm the date filter is in the SQL. This one check saves a lot of grief. It is the sort of thing we build into our Power BI consulting work as a standard verification, not an optional extra.

When incremental refresh is not the answer

Incremental refresh makes scheduled refreshes cheap. It does not make your data live. There is still a refresh cycle, and between cycles your numbers are as old as your last run. For most reporting that is completely fine. Yesterday's sales, last month's margins, the rolling twelve-month trend: none of that needs to be to-the-second.

But some things do. A logistics operations board showing trucks currently in transit. A support desk wallboard showing tickets open right now. A trading or pricing view where a five-minute-old number is a wrong number. For those, import mode with incremental refresh is the wrong tool no matter how you tune it, because the data model is fundamentally a snapshot.

That is where DirectQuery and real-time options come in. DirectQuery leaves the data in the source and queries it live every time someone interacts with the report, so the numbers are always current. The tradeoff is that every slice and filter fires a query at your source database, so performance depends entirely on how fast that source is and how well it is indexed, and a slow warehouse makes for a miserable DirectQuery report. Power BI also supports true streaming datasets and push data for genuine real-time scenarios, where data is pushed into the model as events happen rather than pulled on a schedule.

Increasingly the interesting middle ground is hybrid tables: historical partitions in import mode for speed, and the most recent partition in DirectQuery for freshness, in the one table. Old data is fast because it is in memory, today's data is live because it is queried direct. You get most of the speed and most of the freshness. It is more moving parts to reason about, and it needs Premium or Fabric capacity, but for the "fast history plus live today" requirement it is genuinely the right shape. If you are already on Fabric this fits neatly into the broader platform, which is a conversation we have a lot through our Microsoft Fabric work.

My honest take

Incremental refresh is one of the highest-return things you can do to a large Power BI model, and it is criminally underused because the model works fine without it right up until the day it does not. If you have any import model over a few million rows and you have never set it up, that is your next job, not more capacity.

The two mistakes I see most: picking a refresh window without actually knowing how far back your source data changes, and not verifying query folding so the whole thing silently runs in the slow path. Both are avoidable in an afternoon if you check rather than assume.

And keep the real-time question separate in your head. "My refresh is too slow" is an incremental refresh problem. "My data is too old" is a DirectQuery or hybrid or streaming problem. They feel similar and they have completely different answers, and reaching for the wrong one is how you end up with a live report hammering a database that was never built to serve it.

If you are staring at a refresh that keeps timing out, or you are trying to work out whether a dashboard genuinely needs to be live or just needs to be fast, this is the kind of thing we sort out regularly as part of a broader business intelligence practice. Happy to have a chat about where your models are and what they actually need before anyone spends money on more capacity they might not need.