Power BI Incremental Refresh for Tables Over 10 Million Rows

Incremental Refresh is what separates a Power BI dataset that loads in 30 seconds from one that takes 90 minutes and times out. For tables under 1M rows you can ignore it. For tables over 10M rows you cannot afford to. This article is the production playbook for configuring Incremental Refresh on the largest tables a Power BI dataset is likely to hold — typically account_move_line, stock_move, web telemetry, or transactional logs — and the gotchas that cost teams a Saturday when they get it wrong.

We assume you are working in Power BI Desktop 2026-01 or later, the dataset is published to a workspace backed by either Pro (1 GB cap), PPU (100 GB), or Premium / Fabric F-SKU (400 GB). The patterns below scale from 10M rows to a billion+ depending on capacity.

Key Takeaways

• Incremental Refresh partitions a table by date so only recent partitions refresh, not the whole table.
• The two parameters RangeStart and RangeEnd are non-negotiable — they must exist exactly with those names and DateTime type.
• Query folding to the source database is mandatory; if the M query does not fold, IR refreshes the entire table anyway.
• The refresh policy has two windows: how much history to store, and how much recent data to refresh on each run.
• Detect Data Changes (CDC-like polling) avoids refreshing partitions whose source rows have not changed.
• For tables above 100M rows, hybrid mode (Import historic + DirectQuery hot) keeps dashboards fast and current.
• You can NOT preview incremental partitions in Power BI Desktop; first refresh in the Service is the moment of truth.
• Incremental Refresh requires Pro, PPU, or capacity SKU; not available for Free workspaces.

---

Why Incremental Refresh Exists

A standard Power BI Import refresh re-pulls every row of every table on every refresh. For a 100M-row fact table sourced from PostgreSQL, that is 30+ minutes of network and CPU on every refresh — and you typically only want the last day's rows. The other 99.97% of the data has not changed.

Incremental Refresh tells Power BI to partition the table by date, refresh only the recent partitions, and leave older partitions untouched. The refresh that took 30 minutes drops to 2-3 minutes. The dataset that was hitting the 1 GB Pro limit fits comfortably. Storage and CPU costs both fall by 90%+.

The cost: you must configure it correctly. Get it wrong and you either refresh nothing (silent failure) or refresh everything (defeats the point).

The Four Mandatory Pieces

1. RangeStart and RangeEnd parameters

In Power Query (Transform Data → Manage Parameters), create two parameters:

• Name: RangeStart. Type: Date/Time. Suggested: 2024-01-01 00:00:00.
• Name: RangeEnd. Type: Date/Time. Suggested: 2024-01-02 00:00:00.

The names are case-sensitive. Power BI looks for exactly RangeStart and RangeEnd. The Service overrides these at refresh time with each partition's date window.

2. Filter the table by RangeStart / RangeEnd

In the M query for your fact table, add a filter step:

let
    Source = PostgreSQL.Database("warehouse.example.com", "analytics"),
    account_move_line = Source{[Schema="public",Item="account_move_line"]}[Data],
    Filtered = Table.SelectRows(
        account_move_line,
        each [date] >= RangeStart and [date] < RangeEnd
    )
in
    Filtered

The filter must use >= for RangeStart and < for RangeEnd so partitions don't double-count. This is the convention Power BI's IR engine assumes.

3. Configure Incremental Refresh on the table

Right-click the table → Incremental refresh.

• Archive data starting: how much history to keep (e.g., 5 years).
• Incrementally refresh data starting: the recent window to refresh on each run (e.g., 7 days).
• Optional: Detect data changes — pick a LastModified column to skip partitions whose data hasn't changed.
• Optional: Only refresh complete days — skip the partial in-flight day.

The two windows mean: store 5 years, refresh the last 7 days each time. Partitions older than 7 days but younger than 5 years stay frozen.

4. Verify query folding

Before publishing, right-click the last step in Power Query → "View Native Query." If you see SQL, query folding works. If the option is grayed out, your M query has broken folding (typically by using a non-foldable function).

Common folding killers:

• Table.AddColumn with custom M code (use SQL views instead).
• Table.Buffer.
• Type changes that the source can't handle.
• Replace Values with non-string substitutions.

If folding breaks, IR will refresh the entire table even though it appears partitioned. This is the most common silent failure.

A Worked Example: Odoo account_move_line

This is a real-world scenario from an ECOSIRE deployment. The client had 47M rows across 5 years of postings. Full refresh took 22 minutes.

Setup

1. Create RangeStart and RangeEnd Date/Time parameters.
2. M query:

let
    Source = PostgreSQL.Database("odoo-replica.example.com", "odoo_prod"),
    account_move_line = Source{[Schema="public",Item="account_move_line"]}[Data],
    JoinedToMove = Table.NestedJoin(
        account_move_line,
        {"move_id"},
        Source{[Schema="public",Item="account_move"]}[Data],
        {"id"},
        "move",
        JoinKind.LeftOuter
    ),
    ExpandedMove = Table.ExpandTableColumn(
        JoinedToMove,
        "move",
        {"date", "state"},
        {"move_date", "move_state"}
    ),
    PostedOnly = Table.SelectRows(ExpandedMove, each [move_state] = "posted"),
    DateFiltered = Table.SelectRows(
        PostedOnly,
        each [move_date] >= RangeStart and [move_date] < RangeEnd
    ),
    TypedDate = Table.TransformColumnTypes(DateFiltered, {{"move_date", type date}})
in
    TypedDate

Verify folding works at every step. If Table.NestedJoin breaks folding (it sometimes does on PostgreSQL), replace with a database view that does the join in SQL.

Refresh policy

• Archive: 5 years.
• Refresh: 30 days (postings can be amended within a fiscal close window).
• Detect data changes: write_date column from account_move.
• Only refresh complete days: off (we want today's data live).

Results

The first refresh in the Service still takes the full duration because it builds every historical partition. After that, every refresh only touches the last 30 days plus today, with write_date filtering further.

Detect Data Changes — The Hidden Win

Detect Data Changes uses a polling column (write_date, updated_at, _etl_loaded_at) to skip partitions whose data has not changed. Without it, IR refreshes every partition in the "incremental" window even if a partition has no new rows.

For Odoo's account_move_line, write_date works perfectly because Odoo updates it on any row change. The IR engine queries MAX(write_date) per partition; if it matches the last refresh, partition is skipped.

Net effect: a typical day might refresh only 2-3 of the last 30 partitions (today plus any backdated postings). Refresh time drops below a minute.

Pitfall: the Detect Data Changes column must exist in the source AND be foldable. If the column is computed in M, it won't fold and detection is bypassed.

Hybrid Mode: Import + DirectQuery

Once your hot data needs to be more current than the refresh schedule allows (e.g., near-real-time), Power BI Premium / PPU offers Hybrid Tables. The historic partitions stay Import (cached), but the most recent partition (e.g., today) is DirectQuery (live to source).

Configure: in the Incremental Refresh dialog, check "Get the latest data in real time with DirectQuery."

Hybrid is the production answer for dashboards that need to show "current quarter through right now" alongside multi-year history. The catch: your source database must handle the DirectQuery load on the hot partition.

Common Pitfalls

1. Query folding silently breaks

You change a step in Power Query, suddenly IR refreshes the entire table. The dataset doubles in size, refresh time triples. Always verify folding after Power Query edits with "View Native Query."

2. RangeStart / RangeEnd type mismatch

If the parameters are typed as Date instead of Date/Time, IR refuses to apply. Always Date/Time. The filter on the column may need DateTime.From(RangeStart) if the source column is text.

3. First refresh runs forever

The first Service refresh after enabling IR builds every historical partition. For a 5-year, 50M-row table, this can take hours and may time out. Use the XMLA endpoint with Tabular Editor to pre-create partitions or refresh chunked windows.

4. Partition counts explode

If you choose daily partitions for 5 years × 5 fact tables, you have ~9,000 partitions per dataset. The model becomes slow to refresh metadata. Use monthly partitions for older history (Storage period: 5 years + RangePolicyType: Month) and only daily for the recent window.

5. Power BI Desktop doesn't show partitions

In Desktop you see one table. Partitions only exist after the first Service refresh. Don't try to debug IR locally — publish, refresh, then inspect.

6. Source TimeZone mismatches

If the source DB stores created_at in UTC and your Power BI parameters drift to local time, the partition boundaries are off. Always use UTC in source and parameters; convert to local in the Date table for display only.

7. Disabling IR re-imports everything

If you turn off IR on a table, the next refresh ignores partitions and reloads from scratch. For a 50M-row table this can crash the dataset. Plan IR changes during a maintenance window.

Operational Best Practices

1. Start with monthly partitions for RangePolicyType until you genuinely need daily granularity. Fewer partitions = faster metadata.
2. Set Detect Data Changes on every IR table. Free 90% performance win.
3. Pre-create partitions via XMLA for the first refresh of large tables. Tabular Editor's RefreshPolicy script is one line.
4. Monitor refresh history in the Service. A refresh that suddenly takes 10x normal time = folding broken.
5. Document the IR config in source control. Refresh policies are easy to lose track of across model versions.
6. Refresh complete days only for late-arriving data. If your warehouse loads at 3am, set IR to skip in-progress days.

When NOT to Use Incremental Refresh

• Tables under ~5M rows. Overhead exceeds savings.
• Tables that don't have a reliable date column.
• Tables where any row, including ancient history, can change. (IR works but you must refresh the entire archive window when amendments happen.)
• DirectQuery-only datasets (IR is for Import).

Performance Comparison Table

Real numbers from ECOSIRE deployments:

The pattern is consistent: 90-95% reduction in refresh time after IR is configured correctly.

Frequently Asked Questions

Does Incremental Refresh work with all data sources?

It requires the source to support query folding. PostgreSQL, SQL Server, Snowflake, BigQuery, and Synapse all fold cleanly. SharePoint lists, Excel files, and many SaaS connectors do NOT fold and cannot use IR effectively. Stage those into a database first.

Can I use IR with a Dataflow as the source?

Yes — Dataflows in Power BI Premium support IR upstream, and a downstream dataset can also be IR. Make sure both are configured; otherwise, you double-load.

How do I refresh a single partition manually?

Use the XMLA endpoint with SSMS or Tabular Editor. The TMSL script RefreshType: dataOnly applied to a single partition refreshes just that slice. Useful for backfilling a corrected day without re-running the whole window.

What happens if I republish the dataset?

Republishing typically preserves partitions if the table structure is unchanged. Major schema changes (adding/removing the date column, changing types) can wipe partitions and trigger a full refresh. Test in a dev workspace first.

Where can I get help configuring Incremental Refresh?

ECOSIRE configures IR as part of every Power BI implementation involving large fact tables — typically Odoo finance, ERP analytics, or SaaS telemetry. Talk to our Power BI implementation team or browse Power BI products for IR-ready dataset templates. For Odoo-specific patterns, see our 12 DAX patterns for Odoo deep dive.

---

Incremental Refresh is the single biggest performance lever in Power BI for tables above 10M rows. Configured correctly, it makes the difference between a dataset that scales for years and one that hits the 1 GB Pro ceiling in six months. Start with the four mandatory pieces, verify folding obsessively, and add Detect Data Changes from day one.