Ravindra BagaleCourses & study guides मराठी Track your progress

10. Parameters in Power Query (and Other Kinds of Parameters)

10.8 Incremental Refresh with RangeStart and RangeEnd (Overview)

By Last updated:

Start with a problem

The Orders table has years of history. A full refresh every night takes an hour, but only the last few days change.

Explain simply

Incremental refresh splits the table into date partitions. The Service refreshes only recent partitions and keeps the old ones. It needs two Date/Time parameters named exactly RangeStart and RangeEnd, and a filter >= RangeStart and < RangeEnd.

Show the data

Order ID Order Date City Amount (₹)
301 06-04-2026 Pune 400
302 06-04-2026 Pune 200
303 07-04-2026 Nashik 300
304 04-05-2026 Nashik 150
305 04-05-2026 Kolhapur 450
306 05-05-2026 Pune 250
307 05-05-2026 Nashik 350
308 05-05-2026 Kolhapur 0 (cancelled)

About this data: This table uses columns: Order ID, Order Date, City, Amount (₹). This table uses columns: Setting, Value. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Create RangeStart and RangeEnd, type Date/Time, current values 01-05-2026 00:00 and 01-06-2026 00:00. In Desktop they only limit the preview.

  2. Filter Order Date (as Date/Time): [Order Date] >= RangeStart and [Order Date] < RangeEnd.

    Your result should look like this:

    Rows in Desktop
    304–308 (5 rows, ₹1,200)

  3. Close & Apply › right-click Orders in the Data pane › Incremental refresh.

  4. Turn it on: Archive data starting 3 Years before refresh date; Incrementally refresh 10 Days (optional: Detect data changes, Only refresh complete days) › Apply.
  5. Publish. The first refresh in the Service creates the partitions and loads history; later refreshes load only the last 10 days.

Show the result

Your result should look like this (fictional sample):

Setting Value
RangeStart / RangeEnd in Desktop 01-05-2026 00:00 / 01-06-2026 00:00
Rows in Desktop 5 (₹1,200)
Archive 3 years
Refresh incrementally last 10 days

Explain what happened

In the Service, Power BI replaces RangeStart and RangeEnd for each partition. One side uses >= and the other <, so no row falls into two partitions.

Explore further

  • Check that the filter folds to the source (View Native Query); otherwise every partition still reads all rows.

Detailed reference for this lesson:

Incremental refresh refreshes only recent data (for example the last 10 days) instead of the full history. This makes refresh faster for large Orders tables.

Steps in Power BI

  1. Create two parameters named exactly RangeStart and RangeEnd, both of type Date/Time (current values such as 01-01-2025 00:00 and 01-02-2025 00:00).
  2. Filter the Orders query on Order DateTime: >= RangeStart and < RangeEnd. Use >= on one side and < on the other, so no row is loaded twice. The filter should fold to the source.
  3. Close & Apply. In Report or Table view, right-click the Orders table in the Data pane › Incremental refresh.
  4. Turn it on and set, for example: Archive data starting 3 Years before refresh date; Incrementally refresh data starting 10 Days before refresh date. Optional settings: Get the latest data in real time with DirectQuery (capacity feature), Only refresh complete days, Detect data changes.
  5. Apply and publish. The Service creates partitions on the first refresh.

Good to know

The source should support query folding (SQL databases do). With plain files, the filter works but every file is still read. Once published with incremental refresh, you cannot download the .pbix back from the Service.

Incremental refresh is an advanced topic. For now, the basics are enough – you will learn the details when your job needs it.

Ravindra Bagale's Tip

The most common incremental refresh mistake is a RangeStart/RangeEnd filter that doesn't fold, so Power BI downloads the whole table for every partition. Check View Native Query on the filter step. Also remember that the policy only takes effect after publishing, not in Desktop. Don't make this mistake!

Fix common mistakes

Wrong result Reason Correction
Orders on a boundary date counted twice <= used on both ends >= RangeStart and < RangeEnd
Incremental refresh shows a warning or stays off Parameters named differently or typed Date Exact names, type Date/Time
Refresh is still slow The filter does not fold Use a folding source such as SQL; check View Native Query

Practise independently

Practice task

With RangeStart 06-04-2026 00:00 and RangeEnd 07-04-2026 00:00, which practice orders load? And with RangeEnd 08-04-2026 00:00?

Hints
  • RangeEnd is excluded.
Solution

RangeEnd 07-04: only 06-04 orders, 301 and 302 (₹600). RangeEnd 08-04: 301, 302 and 303 (₹900).

Think like a professional

Incremental refresh is a design decision: agree on the date column, confirm folding, and test with a small range before publishing.

Ravi Sir's Cards for this lesson

Incremental refresh with RangeStart and RangeEnd. Refreshing years of old data every day is slow and puts load on the source.Revision cardWhat is incremental refresh and how do you set it up? Incremental refresh keeps old data stored and refreshes only recent data. You create Date/Time parameters named RangeStart and RangeEnd, filter thInterview card

मराठी cards