Implementing Incremental Refresh Logic in Power Query M: Range Parameters, RangeStart/RangeEnd, and Folding-Compatible Filter Patterns

Introduction

Your sales fact table has 200 million rows spanning five years. Every morning, someone hits Refresh in Power BI Desktop and walks away for 20 minutes while the entire table re-imports from Snowflake. You already know there's a better way — you've heard "incremental refresh" mentioned in every Power BI performance conversation — but when you actually try to configure it, the documentation glosses over the part that matters most: what happens inside the M query, and more critically, why your query has to be written a specific way for incremental refresh to work at all.

Incremental refresh in Power BI Premium and Premium Per User is not magic. It's a mechanical process that depends on two specially named parameters (RangeStart and RangeEnd), a filter you write by hand in M, and the query engine's ability to push that filter down to the source database through a mechanism called query folding. If any one of those three pieces is missing or misconfigured, you get either silent failure (the full table loads anyway) or an outright error. This lesson covers all three pieces in depth.

By the end of this lesson, you'll be able to configure incremental refresh from scratch for a production fact table, write filter expressions that fold correctly against SQL-based sources, diagnose whether your filter is actually folding, and avoid the common mistakes that cause full refreshes to happen even when incremental refresh appears to be set up correctly.

What you'll learn:

• How RangeStart and RangeEnd parameters work and the exact naming and type requirements Power BI enforces
• How to write M filter expressions that are compatible with query folding
• How to inspect the native query to confirm folding is happening
• How to handle timezone offsets and datetime type mismatches that silently break folding
• How to structure queries for sources that don't fold natively, including the tradeoffs involved

---

Prerequisites

You should be comfortable with:

• Power Query M syntax, including let...in expressions and function application
• Basic query folding concepts (you know what it is, even if you haven't debugged it)
• Power BI Desktop and the incremental refresh configuration dialog
• Working with a SQL-based data source (the examples use SQL Server and Snowflake, but the patterns apply broadly)

---

How Incremental Refresh Actually Works Under the Hood

Before writing a single line of M, you need a clear mental model of what incremental refresh is doing, because this model will explain every decision you make in the query.

When incremental refresh is configured, Power BI splits your dataset's date range into time-period partitions — daily, monthly, or whatever granularity you configure. Each partition is a separate import of a time-bounded slice of your data. When a scheduled refresh runs, Power BI only re-imports the partitions that fall within your "refresh period" window (typically the most recent N days or months). Older partitions are considered historical and left alone.

The partitioning works by substituting values into your RangeStart and RangeEnd parameters at runtime. For a monthly partition covering March 2024, Power BI sets RangeStart to 2024-03-01 00:00:00 and RangeEnd to 2024-04-01 00:00:00. It then runs your M query with those values in place, expecting to get back only rows where your date column falls in [RangeStart, RangeEnd).

This means your M query must be written to use these parameters as filters, and it means the filter has to actually work — producing the right rows and, ideally, doing so efficiently by pushing the filter to the source.

There's a common misconception worth dispelling immediately: Power BI does not parse your M query and auto-inject date filters. It substitutes parameter values and runs your query verbatim. If your query doesn't reference RangeStart and RangeEnd, no filtering happens, and you've just partitioned a full table import into redundant chunks.

---

Creating the RangeStart and RangeEnd Parameters

The parameters are not ordinary Power Query parameters in all respects. They have specific requirements you cannot deviate from.

Name: Must be exactly RangeStart and RangeEnd. Case-sensitive. rangeStart, Range_Start, DateStart — none of these will work. Power BI's incremental refresh engine looks for these exact names.

Type: Must be Date/Time. Not Date, not Date/Time/Timezone. The type must be Date/Time in the parameter definition dialog. This is one of the most common sources of frustration, because most date columns in source databases are date (not datetime), and developers naturally set the parameter type to Date to match. This breaks the mechanism.

Current Value: Set this to a plausible recent date during development — for example, 2024-01-01 00:00:00 for RangeStart and 2024-02-01 00:00:00 for RangeEnd. These values are used when you're working in Power BI Desktop before the partitioning engine takes over. They let you preview a real (but bounded) slice of data during development.

To create a parameter in Power BI Desktop, go to the Power Query Editor, select Manage Parameters from the Home ribbon, click New, and fill in the name, type, and current value. Do this for both RangeStart and RangeEnd.

After creating them, your parameter list will show two entries. The types displayed should read "Date/Time" — if they say anything else, go back and correct them before proceeding.

---

Writing the Filter Expression

Here is a production fact table query for an order history table in SQL Server. We'll build it up from scratch.

Starting point — no incremental refresh, just the raw import:

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    RemovedColumns = Table.SelectColumns(
        OrdersTable,
        {"OrderKey", "CustomerKey", "ProductKey", "OrderDateKey",
         "OrderDateTime", "SalesAmount", "Quantity", "ShipDateKey"}
    )
in
    RemovedColumns

This query loads the entire FactOrders table — all 200 million rows. Now we add the incremental refresh filter.

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    FilteredRows = Table.SelectRows(
        OrdersTable,
        each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd
    ),
    RemovedColumns = Table.SelectColumns(
        FilteredRows,
        {"OrderKey", "CustomerKey", "ProductKey", "OrderDateKey",
         "OrderDateTime", "SalesAmount", "Quantity", "ShipDateKey"}
    )
in
    RemovedColumns

Three things to notice about this filter:

1. The filter is applied to the source table, not a derived step. FilteredRows operates on OrdersTable, which came directly from Source. This positioning is critical for query folding. If you applied this filter after RemovedColumns or after any step that breaks folding (like a custom column with an M function), the filter wouldn't fold.

2. The boundary condition is >= on the low end and < on the high end. This is the standard half-open interval pattern. For a monthly partition covering March, you want rows where OrderDateTime >= 2024-03-01 and OrderDateTime < 2024-04-01. The upper boundary is exclusive, meaning rows at exactly midnight on April 1st belong to the April partition, not March. If you use <= on both sides, you'll double-count rows that land exactly on the boundary datetime.

3. RangeStart and RangeEnd are referenced directly by name. They are global in scope — you don't need to import them or pass them as arguments.

---

Confirming Query Folding

Writing a filter is not enough. You need to verify the filter actually folds — meaning the filter condition appears in the SQL sent to the database, not in M code running in the Power Query engine after all rows have been fetched.

Right-click on the FilteredRows step in the Applied Steps panel. If the option View Native Query appears and is not greyed out, folding is happening. Click it, and you'll see the SQL that Power BI is generating. For the query above against SQL Server, you should see something like:

SELECT [OrderKey], [CustomerKey], [ProductKey], [OrderDateKey],
       [OrderDateTime], [SalesAmount], [Quantity], [ShipDateKey]
FROM [dbo].[FactOrders]
WHERE [OrderDateTime] >= '2024-01-01 00:00:00'
  AND [OrderDateTime] < '2024-02-01 00:00:00'

The WHERE clause is there. The database handles the filtering. Only the matching rows travel across the network into Power Query. This is what you want.

If View Native Query is greyed out, folding is not happening, and the full table is being loaded into memory before the M filter runs. For a 200-million-row table, this is catastrophic for performance and defeats the entire purpose of incremental refresh.

---

What Breaks Query Folding (And How to Fix It)

Query folding breaks when Power Query can no longer express a step as a transformation the source system understands. Here are the specific patterns that kill folding in incremental refresh scenarios.

Type Mismatches

The single most common cause of folding failure in incremental refresh. Your OrderDateTime column comes from SQL Server as a datetime column, which Power Query maps to DateTimeZone or DateTime depending on connector settings. If the inferred type doesn't match RangeStart and RangeEnd's DateTime type, Power Query may insert an implicit type conversion that breaks folding.

The fix is to be explicit. Add a type assertion or conversion step immediately after the source, before the filter:

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    TypedTable = Table.TransformColumnTypes(
        OrdersTable,
        {{"OrderDateTime", type datetime}}
    ),
    FilteredRows = Table.SelectRows(
        TypedTable,
        each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd
    ),
    RemovedColumns = Table.SelectColumns(
        FilteredRows,
        {"OrderKey", "CustomerKey", "ProductKey", "OrderDateKey",
         "OrderDateTime", "SalesAmount", "Quantity", "ShipDateKey"}
    )
in
    RemovedColumns

Table.TransformColumnTypes folds against SQL-based connectors in most cases, so inserting it between the source and the filter doesn't break folding — it just adds a CAST to the native query, which is fine.

Custom Columns Using M Functions

If you add a custom column using an M function that has no SQL equivalent — Text.Contains, Date.From, List.Accumulate, or anything from the Date.* and Text.* namespaces — Power Query can no longer express that step in SQL. Every step after the fold-breaking step runs in-memory in M.

This means if your filter comes after a custom column step, it won't fold, even if the filter expression itself is perfectly legal SQL. The fix is to put the filter before any fold-breaking transformations:

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    // Filter FIRST, before anything that breaks folding
    FilteredRows = Table.SelectRows(
        OrdersTable,
        each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd
    ),
    // Now it's safe to add M-only transformations on the smaller dataset
    AddedFiscalYear = Table.AddColumn(
        FilteredRows,
        "FiscalYear",
        each Date.Year(Date.AddMonths([OrderDateTime], -6)),
        Int64.Type
    ),
    RemovedColumns = Table.SelectColumns(
        AddedFiscalYear,
        {"OrderKey", "CustomerKey", "ProductKey", "OrderDateKey",
         "OrderDateTime", "FiscalYear", "SalesAmount", "Quantity"}
    )
in
    RemovedColumns

The filter on FilteredRows still folds because it's applied directly to OrdersTable. The fiscal year calculation runs in M on the already-filtered (small) dataset. This is the right architecture.

Using DateTime.LocalNow() or DateTime.FixedLocalNow()

Sometimes developers try to make the filter "dynamic" by replacing RangeStart or RangeEnd with DateTime.LocalNow():

// DO NOT DO THIS for incremental refresh
FilteredRows = Table.SelectRows(
    OrdersTable,
    each [OrderDateTime] >= DateTime.LocalNow() - #duration(30, 0, 0, 0)
        and [OrderDateTime] < DateTime.LocalNow()
)

This breaks folding because DateTime.LocalNow() is a volatile M function that returns different values on each call and has no SQL equivalent. More importantly, it defeats the entire incremental refresh mechanism — Power BI cannot inject partition boundaries if you've hardcoded your own date logic.

Use RangeStart and RangeEnd. Always. Let the partitioning engine control the boundaries.

---

Handling Timezone Offsets

Production databases often store timestamps in UTC while your report consumers interpret data in local time. This creates a problem: if your source data has UTC timestamps and your partition boundaries are in local time (or vice versa), rows near partition boundaries may land in the wrong partition.

The standard approach is to ensure everything is consistently in UTC in the M layer, and handle timezone presentation in DAX or report-layer transformations.

If your source column is stored as datetimezone (SQL Server's datetimeoffset), convert to UTC explicitly:

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    // Convert datetimeoffset to UTC datetime before filtering
    NormalizedTable = Table.TransformColumns(
        OrdersTable,
        {{"OrderDateTimeOffset", DateTimeZone.RemoveZone, type datetime}}
    ),
    FilteredRows = Table.SelectRows(
        NormalizedTable,
        each [OrderDateTimeOffset] >= RangeStart
            and [OrderDateTimeOffset] < RangeEnd
    )
in
    FilteredRows

A more folding-friendly approach — if your source database supports it — is to filter on the UTC column directly in SQL and do any timezone conversion in DAX using USERELATIONSHIP against a time zone–aware date table. This keeps the M layer simple and foldable.

---

Snowflake-Specific Patterns

Snowflake introduces some nuances. The Power BI Snowflake connector supports query folding, but column types and the connector configuration affect whether folding works for your incremental refresh filter.

Snowflake commonly stores event timestamps as TIMESTAMP_NTZ (no timezone) or TIMESTAMP_LTZ (local timezone). The Snowflake connector maps these to DateTimeZone in Power Query by default.

Here's a production-ready Snowflake incremental refresh query:

let
    Source = Snowflake.Databases(
        "xy12345.snowflakecomputing.com",
        "PROD_WH",
        [CreateNavigationProperties=false]
    ),
    SalesFact = Source
        {[Name="ANALYTICS_DB"]}[Data]
        {[Name="SALES"]}[Data]
        {[Name="FACT_ORDERS"]}[Data],
    // Snowflake TIMESTAMP_NTZ comes in as DateTimeZone — normalize it
    TypedTable = Table.TransformColumnTypes(
        SalesFact,
        {{"ORDER_TIMESTAMP", type datetime}}
    ),
    FilteredRows = Table.SelectRows(
        TypedTable,
        each [ORDER_TIMESTAMP] >= RangeStart
            and [ORDER_TIMESTAMP] < RangeEnd
    ),
    SelectedColumns = Table.SelectColumns(
        FilteredRows,
        {"ORDER_KEY", "CUSTOMER_KEY", "PRODUCT_KEY",
         "ORDER_TIMESTAMP", "NET_AMOUNT", "QUANTITY"}
    )
in
    SelectedColumns

Check the native query on FilteredRows. You should see a Snowflake SQL statement with a WHERE clause. If you see the Snowflake connector is using a TRY_CAST or CONVERT in the generated SQL, that's the type transformation folding — it's expected and fine.

---

Working with Date Columns Instead of DateTime Columns

A common production scenario: your source table's timestamp column is a plain date column, not datetime. The canonical example is an orders table where OrderDate is date in the source, because nobody needs sub-day precision for daily order dates.

The problem: RangeStart and RangeEnd are DateTime (required by Power BI). You need to compare a Date column against DateTime values. If you just write:

each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd

Power Query may or may not fold this correctly depending on the connector, and you'll likely get a type mismatch warning. The clean approach is to convert the DateTime parameters to Date before the comparison:

let
    Source = Sql.Database("prod-sqlserver.company.com", "SalesDB"),
    OrdersTable = Source{[Schema="dbo", Item="FactOrders"]}[Data],
    // Convert DateTime parameters to Date for comparison with date column
    StartDate = DateTime.Date(RangeStart),
    EndDate = DateTime.Date(RangeEnd),
    FilteredRows = Table.SelectRows(
        OrdersTable,
        each [OrderDate] >= StartDate and [OrderDate] < EndDate
    )
in
    FilteredRows

This pattern folds correctly against SQL Server, Azure SQL, and most ANSI SQL sources because DateTime.Date() on a parameter value is evaluated once before the query runs — it's a constant from the query engine's perspective, not a per-row function call. The resulting WHERE clause looks like:

WHERE [OrderDate] >= '2024-01-01'
  AND [OrderDate] < '2024-02-01'

Clean, foldable, correct.

---

Handling Sources That Don't Fold

Sometimes you're working with a source that simply doesn't support query folding — Excel files, SharePoint lists, poorly-configured OData endpoints, or REST APIs. Incremental refresh in its pure form isn't possible here, because Power Query must load the full dataset before filtering.

You have two realistic options:

Option 1: Accept full loads with filtered partitions. You can still use RangeStart and RangeEnd as filters even without folding. Power BI will load the entire source for each partition refresh, then apply the M filter in memory. This is inefficient, but it does mean you're only storing the right rows per partition — and it may be acceptable if your source is fast enough and small enough. The configuration is the same; you just accept the performance cost.

Option 2: Use a staging layer. Instead of connecting Power BI directly to the unfolding source, ETL the data into a SQL database first (Azure SQL, Synapse, Databricks, Snowflake), then point your Power BI incremental refresh query at the SQL layer. This is the right long-term architecture for production workloads.

For Option 1, be aware that the "Detect Data Changes" feature in the incremental refresh configuration dialog can help reduce unnecessary refreshes even when folding isn't happening — it checks a configured column (like a max UpdatedAt timestamp) before deciding whether to refresh a partition.

---

Hands-On Exercise

Build a fully functional incremental refresh query for the following scenario.

Scenario: You have a SQL Server table called dbo.WebEvents on a server named analytics-sql.company.com in a database named AnalyticsDB. The table has the following relevant columns:

• EventId (bigint)
• UserId (int)
• EventType (varchar)
• EventTimestamp (datetime2) — this is your partition column
• PagePath (nvarchar)
• SessionDurationSeconds (int)

You need to configure incremental refresh that stores 2 years of data and refreshes the last 14 days on each run.

Step 1: Create the parameters.

In Power Query Editor, go to Manage Parameters. Create RangeStart with type Date/Time and current value 2024-01-01 00:00:00. Create RangeEnd with type Date/Time and current value 2024-02-01 00:00:00. These give you one month of preview data while building.

Step 2: Write the M query.

Create a new blank query and paste this:

let
    Source = Sql.Database("analytics-sql.company.com", "AnalyticsDB"),
    WebEventsRaw = Source{[Schema="dbo", Item="WebEvents"]}[Data],
    // Ensure datetime2 maps to datetime type for comparison
    TypedTable = Table.TransformColumnTypes(
        WebEventsRaw,
        {{"EventTimestamp", type datetime}}
    ),
    // Incremental refresh filter — must fold
    FilteredRows = Table.SelectRows(
        TypedTable,
        each [EventTimestamp] >= RangeStart
            and [EventTimestamp] < RangeEnd
    ),
    // Select only needed columns
    FinalColumns = Table.SelectColumns(
        FilteredRows,
        {"EventId", "UserId", "EventType",
         "EventTimestamp", "PagePath", "SessionDurationSeconds"}
    )
in
    FinalColumns

Step 3: Verify folding.

Right-click the FilteredRows step and select View Native Query. Confirm you see a WHERE clause referencing EventTimestamp with date literals matching your parameter current values.

Step 4: Configure incremental refresh.

Back in Power BI Desktop's model view, right-click the WebEvents table and select Incremental refresh. Enable it. Set:

• Store rows in the last: 2 Years
• Refresh rows in the last: 14 Days
• Leave "Detect data changes" unchecked for now

Step 5: Validate.

Change RangeStart's current value to 2024-06-01 00:00:00 and RangeEnd to 2024-06-15 00:00:00. Refresh the preview in Power Query. You should see only rows from that 14-day window, and the row count should reflect approximately 14 days of event volume, not the full table.

---

Common Mistakes & Troubleshooting

Mistake 1: Parameters named incorrectly or wrong type

Symptom: Incremental refresh configuration dialog says "The query must reference 'RangeStart' and 'RangeEnd' parameters."

Fix: Open Manage Parameters. Confirm the names are exactly RangeStart and RangeEnd (case-sensitive). Confirm the type is Date/Time, not Date or Date/Time/Timezone.

---

Mistake 2: Full table loads despite incremental refresh being configured

Symptom: Refresh takes as long as a full load. Source system shows full table scans in query logs.

Diagnosis: Check the native query for your filter step. If it's greyed out, folding isn't happening. Review the steps between your source and the filter for anything that breaks folding (custom M functions, merges against non-foldable sources, added index columns).

Fix: Reorder steps so the RangeStart/RangeEnd filter comes immediately after the source table reference, before any transformations that break folding.

---

Mistake 3: Filter on wrong column

Symptom: Incremental refresh is configured but rows from old partitions keep appearing in refresh window partitions, or partition data is inconsistent.

Context: You filter on a LoadedAt audit column (when the ETL ran) instead of OrderDateTime (when the event occurred). Historical backfills or late-arriving data then falls into incorrect partitions.

Fix: Be deliberate about which column represents the data's natural time boundary. For most fact tables, this is the event timestamp, not the ETL audit timestamp. Document this decision.

---

Mistake 4: Using DateTime.From() on the column side

Symptom: Query takes much longer than expected, View Native Query is greyed out on filter step.

Wrong approach:

// This forces a per-row function call, breaking folding
each DateTime.From([OrderDate]) >= RangeStart
    and DateTime.From([OrderDate]) < RangeEnd

Right approach:

// Convert parameters once, keep column untouched
let StartDate = DateTime.Date(RangeStart), EndDate = DateTime.Date(RangeEnd)
in Table.SelectRows(t, each [OrderDate] >= StartDate and [OrderDate] < EndDate)

---

Mistake 5: Publishing without verifying partition behavior

Symptom: After publishing, the first refresh takes as long as a full load (expected — this is the initial historical load), but subsequent refreshes are also slow.

Diagnosis: Check the Power BI Premium capacity metrics app or workspace monitoring to see how many partitions are being refreshed each run. If all partitions refresh every time, the partition boundaries aren't being applied correctly.

Fix: Confirm that your published dataset's table shows multiple partitions in SQL Server Management Studio (for XMLA endpoint access) or in Tabular Editor. Each partition should have a different date range in its M expression. If all partitions show the same M expression without date substitution, the RangeStart/RangeEnd wiring failed.

---

Mistake 6: Testing in Power BI Desktop vs. production behavior

Symptom: Works perfectly in Desktop, but Premium service refresh loads the full table.

Context: Power BI Desktop evaluates the query using the current parameter values you've set. The service substitutes partition-specific values at runtime. If your Desktop test looks fine but the service misbehaves, the most common cause is a connector behavior difference between Desktop and the service — particularly around authentication, gateway configuration, or connector version.

Fix: Use the XMLA endpoint to connect Tabular Editor to your published dataset. Inspect the M expression of each partition. Confirm RangeStart and RangeEnd have been substituted with actual datetime literals for each partition.

---

Summary & Next Steps

Incremental refresh in Power BI is built on three mechanical requirements: correctly named and typed DateTime parameters, a filter expression in M that uses those parameters, and a query structure that allows that filter to fold to the source system. Get all three right, and you can refresh a 200-million-row fact table in minutes instead of hours. Miss any one of them, and you get a full load disguised as an incremental one.

The patterns you've learned in this lesson:

• RangeStart and RangeEnd must be Date/Time type, exact names, no exceptions
• Write the filter before any fold-breaking M transformations
• Convert parameters to match the column type (DateTime.Date(RangeStart) for date columns), never convert columns to match parameters
• Use View Native Query to verify the WHERE clause is present before trusting that folding works
• Handle timezone normalization at the M layer before the filter, not after

Where to go next:

The natural continuation from here is hybrid tables, which combine an incremental refresh import partition for history with a DirectQuery partition for real-time data. This requires everything you've learned here plus an understanding of how DirectQuery partition expressions differ from import expressions. The Tabular Editor approach to partition management (using the XMLA endpoint) is also worth learning — it gives you fine-grained control over partition definitions that the Power BI Desktop UI doesn't expose.

Beyond that, if you're working with Azure Synapse Analytics or Databricks as your source, look into how those connectors handle folding differently from SQL Server and Snowflake — the connector-specific documentation on folding support is your guide, and the principles you've applied here transfer directly.