{"id":1271,"date":"2026-04-01T13:35:39","date_gmt":"2026-04-01T12:35:39","guid":{"rendered":"https:\/\/knitteddata.com\/?p=1271"},"modified":"2026-04-01T13:36:35","modified_gmt":"2026-04-01T12:36:35","slug":"only-knit-the-new-rows-incremental-refresh","status":"publish","type":"post","link":"https:\/\/knitteddata.com\/index.php\/2026\/04\/01\/only-knit-the-new-rows-incremental-refresh\/","title":{"rendered":"Only Knit the New Rows: Incremental Refresh"},"content":{"rendered":"\n<p>You get only a few new rows in your data source, and yet you refresh full tables in your semantic model every day.<\/p>\n\n\n\n<p>Or you change one small thing in Power Query and suddenly you are waiting an hour for the local model to reload. Or your data no longer fits into a .pbix. Or you hit a timeout during refresh.<\/p>\n\n\n\n<p>Sounds familiar?<\/p>\n\n\n\n<p>There is a smarter way: <strong>Incremental Refresh in Power BI<\/strong>. Instead of reloading everything, you refresh only a defined time window and leave the rest in peace. That usually means shorter refresh times, fewer timeouts, and semantic models that scale much better.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">How it works<\/h4>\n\n\n\n<p>With Incremental Refresh, Power BI creates <strong>dynamic partitions<\/strong>. Depending on your refresh policy, some partitions refresh on schedule and others stay untouched.<\/p>\n\n\n\n<p>How partitions look and how they behave deserves its own post. For now, you only need one key idea:<\/p>\n\n\n\n<p>You define time-based rules, and Power BI turns them into partitions behind the scenes.<\/p>\n\n\n\n<p>For a table with a <strong>DateTime column<\/strong> (a Date column transformed to DateTime is fine), you can define three \u201czones\u201d:<\/p>\n\n\n\n<p><strong>Archived<\/strong><br>Data that is loaded once and then left alone.<\/p>\n\n\n\n<p><strong>Incremental<\/strong><br>Data that is refreshed on schedule.<\/p>\n\n\n\n<p><strong>Real time<\/strong><br>The newest slice queried using DirectQuery.<\/p>\n\n\n\n<p>Data older than your configured archive window is removed from the model. This is the classic <strong>rolling window<\/strong> pattern.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"837\" height=\"106\" src=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-04-01-141238.png\" alt=\"\" class=\"wp-image-1272\" srcset=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-04-01-141238.png 837w, https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-04-01-141238-300x38.png 300w, https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-04-01-141238-768x97.png 768w\" sizes=\"auto, (max-width: 837px) 100vw, 837px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Requirements<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Plans<\/h4>\n\n\n\n<p>Incremental refresh is supported for:<br>Power BI Pro<br>Power BI Premium<br>Premium per user<br>Power BI Embedded<\/p>\n\n\n\n<p>The <strong>real time DirectQuery<\/strong> option is supported only for:<br>Premium<br>Premium per user<br>Power BI Embedded<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Data sources<\/h4>\n\n\n\n<p>Incremental refresh and real time work best with structured, relational sources such as SQL databases and Azure Synapse.<br>Your data source must support filtering by date.<br>All partitions must query data from a single source and use one storage mode, Import or DirectQuery.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">1. Create RangeStart and RangeEnd parameters<\/h3>\n\n\n\n<p>In Power BI Desktop, select <strong>Transform data<\/strong> to open Power Query Editor.<br>Select <strong>Manage parameters<\/strong>, then create two parameters:<\/p>\n\n\n\n<p><strong>RangeStart<\/strong><br><strong>RangeEnd<\/strong><\/p>\n\n\n\n<p>Both must be type <strong>DateTime<\/strong>.<\/p>\n\n\n\n<p>In Power BI Service, Power BI overrides these values per partition:<br>RangeStart becomes the partition start<br>RangeEnd becomes the partition end<\/p>\n\n\n\n<p>Set the current values to a small window that loads fast. In Desktop, you will only see data from that window.<\/p>\n\n\n\n<figure class=\"wp-block-video\"><video height=\"1080\" style=\"aspect-ratio: 1920 \/ 1080;\" width=\"1920\" autoplay controls loop muted src=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Video-Project-19.mp4\" playsinline><\/video><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">2. Filter the table using the parameters<\/h3>\n\n\n\n<p>Find your DateTime column and add a filter:<\/p>\n\n\n\n<p>DateTimeColumn &gt;= RangeStart<br>DateTimeColumn &lt; RangeEnd<\/p>\n\n\n\n<p>That \u201cless than\u201d on RangeEnd matters. It prevents overlap between partitions.<\/p>\n\n\n\n<figure class=\"wp-block-video\"><video height=\"1080\" style=\"aspect-ratio: 1920 \/ 1080;\" width=\"1920\" autoplay controls loop muted src=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Video-Project-20.mp4\" playsinline><\/video><\/figure>\n\n\n\n<p>If you do not see the option to use parameters in the filter, check this setting in Desktop:<\/p>\n\n\n\n<p>File -&gt; Options and settings -&gt; Options -&gt; Power Query Editor -&gt; Parameters<br>Enable \u201cAlways allow parameterization in data source and transformation dialogs\u201d.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"814\" src=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-03-18-130937-1024x814.png\" alt=\"\" class=\"wp-image-1277\" srcset=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-03-18-130937-1024x814.png 1024w, https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-03-18-130937-300x239.png 300w, https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-03-18-130937-768x611.png 768w, https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Screenshot-2026-03-18-130937.png 1498w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">3. Set the incremental refresh policy<\/h3>\n\n\n\n<p>Switch to <strong>Model view<\/strong> in Power BI Desktop.<\/p>\n\n\n\n<p>Select the table, open the menu (\u2026), and choose <strong>Incremental refresh<\/strong>.<\/p>\n\n\n\n<p>Enable \u201cIncrementally refresh this table\u201d and configure the ranges:<\/p>\n\n\n\n<p><strong>Archive data starting \u2026 before refresh date<\/strong><br>How far back you keep history in the model. Older partitions become archived.<\/p>\n\n\n\n<p><strong>Incrementally refresh data \u2026 before refresh date<\/strong><br>How much recent data gets refreshed each time.<\/p>\n\n\n\n<p>Optional settings:<\/p>\n\n\n\n<p><strong>Get the latest data in real time with DirectQuery<\/strong><br>Premium required.<\/p>\n\n\n\n<p><strong>Only refresh complete periods<\/strong><br>Useful if you want full-day partitions only.<\/p>\n\n\n\n<p><strong>Detect data changes<\/strong><br>Refresh only rows that changed, based on a \u201clast modified\u201d style column.<\/p>\n\n\n\n<figure class=\"wp-block-video\"><video height=\"1080\" style=\"aspect-ratio: 1920 \/ 1080;\" width=\"1920\" autoplay controls loop muted src=\"https:\/\/knitteddata.com\/wp-content\/uploads\/2026\/04\/Video-Project-21.mp4\" playsinline><\/video><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">4. Publish and run the first refresh<\/h3>\n\n\n\n<p>Publish the semantic model to Power BI Service.<br>Trigger a manual refresh for the initial load, then configure your scheduled refresh.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">My real life learnings<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Snowflake and DateTime folding<\/h4>\n\n\n\n<p>I once had a table with a Date column and converted it to DateTime in Power Query. That worked fine with other sources, but with Snowflake the query stopped folding.<\/p>\n\n\n\n<p>The type conversion happened after data retrieval, so the RangeStart and RangeEnd filter did not reduce the data at the source. Result: incremental refresh was set up, but refresh still took forever.<\/p>\n\n\n\n<p>If possible, have a proper DateTime column already in the source, and always check that the query folds.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Initial load can still be too big<\/h4>\n\n\n\n<p>Incremental refresh helps with ongoing refreshes, but the first refresh still loads the full configured history. That means the same refresh limits still apply.<\/p>\n\n\n\n<p>Power BI Pro models have a 2 hour refresh limit and do not support the real time DirectQuery option.<br>Premium capacity models have a 5 hour refresh limit.<\/p>\n\n\n\n<p>If your reason for switching was timeouts, the initial load can still be the bottleneck. There is a workaround for this. I will cover it in a separate post.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Overlapping RangeStart and RangeEnd<\/h4>\n\n\n\n<p>Be careful with your filter operators. I once used \u201cafter or equal\u201d and \u201cbefore or equal\u201d and my numbers were too high.<\/p>\n\n\n\n<p>Power BI creates partitions where one ends exactly where the next begins. If you include both ends, boundary dates can land in two partitions.<\/p>\n\n\n\n<p>Use:<br>DateTimeColumn &gt;= RangeStart<br>DateTimeColumn &lt; RangeEnd<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">DateTime column size<\/h4>\n\n\n\n<p>Pick your DateTime column wisely. If you use a timestamp with many unique values, compression will be worse. Power BI compresses best when values repeat.<\/p>\n\n\n\n<p>In most models I use a Date column and convert it to DateTime so the time is always 00:00:00. That keeps compression similar to a Date column.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You get only a few new rows in your data source, and yet you refresh&hellip;<\/p>\n","protected":false},"author":2,"featured_media":1280,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,30],"tags":[34,42,41],"class_list":["post-1271","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-fabric","category-power-bi","tag-data-modelling","tag-incremental-refresh","tag-power-bi"],"_links":{"self":[{"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/posts\/1271","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/comments?post=1271"}],"version-history":[{"count":1,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/posts\/1271\/revisions"}],"predecessor-version":[{"id":1279,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/posts\/1271\/revisions\/1279"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/media\/1280"}],"wp:attachment":[{"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/media?parent=1271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/categories?post=1271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/knitteddata.com\/index.php\/wp-json\/wp\/v2\/tags?post=1271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}