Why incremental refresh does nothing on a SharePoint list in Power BI

23 October 2026

Tags: Relational databasesReportingDesktopCommand line

You followed the guide. Two parameters called RangeStart and RangeEnd, a filter on the Modified column, a policy that keeps two years and refreshes the last ten days, and a publish to the service. The first refresh took longer than any refresh before it. The second took as long as the old full refresh did. A week later nothing has improved, and the Fabric Community thread you found ends in a single reply: “SharePoint lists are not foldable.”

That reply is usually right, and this post explains what it means and how to prove it on your own report. It goes deeper on one cause from our post on slow and failing Power BI refreshes on SharePoint lists, which covers throttling, lookup joins and the connector’s join limit as well. The Microsoft facts here come from Microsoft Learn, linked where they appear. Where Learn is silent, we say so.

Why does incremental refresh not work on a SharePoint list?

Incremental refresh splits a table into date partitions and refreshes only the recent ones. To do that, the service rewrites RangeStart and RangeEnd for each partition and runs the query once per partition. The saving depends on the source answering each of those queries with only the rows in that date range.

Microsoft’s incremental refresh overview says what happens when the source cannot do that. If the filter does not fold into the source query, the mashup engine may apply it locally, which means retrieving all rows for the table from the data source. Learn’s words are that this can make incremental refresh slow and run the service or gateway out of resources, “effectively defeating the purpose of incremental refresh”.

The troubleshooting page adds the part that explains the slow first refresh. A query is created for every partition in the model, and when those queries are not folded, the engine does the filtering itself. On a source that ignores the date filter, each partition query can pull the whole list before Power BI throws most of it away. A policy with many partitions can therefore make the first refresh slower than the full refresh it was meant to replace.

What incremental refresh needs from the source

Learn sets out three things a table must offer before a policy helps:

• a date column the filter can use, with RangeStart and RangeEnd defined as date/time parameters
• a source that can apply that filter itself, which Learn calls query folding
• a column that does not change after the row is written, because the troubleshooting page says incremental refresh is not designed for a filter column that the source updates

The overview says incremental refresh works best on structured, relational sources such as SQL Database and Azure Synapse, and can also work on others. Its own example is a Table.SelectRows filter straight after Sql.Database, which folds because SQL Server supports folding. Incremental refresh is available on Pro as well as Premium, so the setting is there for most teams to try. Whether it does anything is up to the source.

Is a SharePoint list query foldable?

Learn does not answer that for the SharePoint Online list connector. The connector page covers the 1.0 and 2.0 implementations, the All and Default views and the join limit, and says nothing about folding either way. The query folding basics page names SQL Server and OData Feed as connectors that commonly support folding, and the SharePoint list connector is not on that short list.

The claim that the date filter never reaches SharePoint comes from the community. Fabric Community threads on SharePoint list incremental refresh report the same thing again and again: the policy saves nothing, and the refresh reads the whole list every time. We have no reason to doubt them, but a forum thread is not documentation, and the answer can depend on the steps in your own query. A filter placed after an expand or a type change on a lookup column may stop folding on a source that folds the plain table. So test it.

Checking your own query with Query Diagnostics

Query Diagnostics records what Power Query sends to the source. Learn describes its Data Source Query column as showing the query or the HTTP request and response sent to the back-end. For a SharePoint list, that means you can read the URLs Power Query calls.

1. Set RangeStart and RangeEnd to a two-day window, as Learn suggests for Desktop.
2. In the Power Query editor, open the Tools tab and choose Start Diagnostics.
3. Refresh the preview of the filtered query, then choose Stop Diagnostics.
4. Open the detailed diagnostics output and filter it to rows with a Data Source Query.
5. Read the request URLs. If none carries a filter on your date column, and the row counts add up to the whole list, the filter did not fold.

If the policy dialog in Desktop also showed a warning that it could not confirm folding, you have two signs pointing the same way. Learn’s troubleshooting page gives a third: a filtered load that takes a long time and works hard most likely is not folding.

What a non-folding policy costs on a 10,000-item list

Take a 10,000-item list with a policy that stores two years and refreshes the last ten days. Here is what each refresh asks of SharePoint, set against Learn’s description of the mechanism.

The middle row is the one people end up in. Every partition query pages through SharePoint’s API, lookup joins and all, and every page counts against the throttling limits that our refresh post describes. The two-hour limit on Pro still applies, and five hours on Premium, so a policy can take a refresh that used to finish and push it past the limit.

We have not published Power BI refresh timings for these three setups. Refresh time on a list depends on its lookup columns, the tenant’s load at that hour, the capacity and any gateway, so a figure from our tenant would not predict yours. What we have measured is the SharePoint side of the third row: SPSync read 10,303 items across four lists, one of them a 10,000-item list, into SQL Server in 1m 52s on our dev tenant, and later runs read only what changed. The section on timing your own list below shows how to fill in the Power BI side for your report.

Does Dataflow Gen2 incremental refresh work on a SharePoint list?

Fabric’s Dataflow Gen2 has its own incremental refresh, set up per query in the dataflow editor. Microsoft’s Dataflow Gen2 incremental refresh page lists a source that supports folding as a recommended prerequisite. It also lists an advanced setting, Require incremental refresh query to fully fold, which Microsoft strongly recommends keeping on. With it on, a query that cannot fold fails validation when you save. With it off, Learn warns that the dataflow may retrieve unnecessary, unfiltered data.

Two more points from the same page apply to lists. The dataflow decides whether a bucket has changed by checking the maximum value of a column you pick, and Learn advises against using the same column for that check and for the filter, because rows that move between buckets can be duplicated in the destination. On a list, the obvious column for both is Modified. And Learn’s FAQ says that incremental refresh can take longer than a full refresh when the overhead of the buckets outweighs the data saved. On a source that does not fold, there is little data saved to set against it.

Workarounds people try on the list itself

Three come up in the threads. Each has a cost.

Calling the SharePoint REST API with Web.Contents and putting RangeStart and RangeEnd into the URL as a filter is the route Learn’s overview describes for sources that do not fold, using the Application Insights API as its example. It can work, and you then own the M code for paging, lookups and retries, and the filter itself has to stay inside the 5,000-item list view threshold, which our post on what breaks past 5,000 items covers.

Switching to the OData Feed connector against the list’s REST endpoint is the second. OData Feed is on Learn’s list of connectors that commonly fold, but Learn does not say how far it folds against SharePoint’s endpoint, so check the requests with Query Diagnostics before you trust it.

The third is to give up on the policy and refresh the full list less often. That is honest about the cost, and it leaves the report a day behind.

Incremental refresh from a SQL replica of the list

The route that matches Learn’s own example is to put the list in a SQL table and point the policy at that. SPSync copies the list into SQL Server, Azure SQL or another database and keeps it current with Microsoft Graph delta queries, every five minutes by default. Power BI then reads a table with the SQL Server connector, and a RangeStart filter after Sql.Database folds into the SQL sent to the server.

let
    Source = Sql.Database("sql01.contoso.local", "reporting"),
    Tasks = Source{[Schema = "dbo", Item = "projects_tasks"]}[Data],
    Filtered = Table.SelectRows(
        Tasks,
        each [sp_created] >= RangeStart and [sp_created] < RangeEnd
    )
in
    Filtered

Every table SPSync writes carries sp_created and sp_modified as date/time columns in UTC, so the filter compares like with like. Lookup and person columns arrive as id columns, so the query has no expand step to stop folding. Run Query Diagnostics on this query too. This time the Data Source Query column shows a SQL statement with a WHERE clause on sp_created.

Choosing the date column: Created or Modified

Filter on sp_created. It never changes after the item is created, which is what the troubleshooting page asks of a filter column. Filtering on sp_modified moves an edited item from an old partition into a new one, and Learn says the service treats that as an insert and a delete, with the delete missed when it falls in the historical range. Use sp_modified for the policy’s Detect data changes option, which Learn describes as refreshing only the days in the refresh period where the data has changed.

Edits to old items and deleted rows

Incremental refresh assumes that old rows mostly stay as they are. A list where people reopen and edit year-old items breaks that assumption on any source. An edit to an item outside the refresh period reaches the SQL table on the next sync, but Power BI does not reload that partition until the period is refreshed again. Size the refresh period to cover how far back your list is edited in practice. On Premium, the XMLA endpoint page describes rebuilding chosen historical partitions with applyRefreshPolicy set to false, for an occasional catch-up.

Deleted items stay in the SPSync table with sp_deleted_at set, so filter them out in the query or read the reporting view SPSync can generate, which leaves deleted rows out. The same caution applies: a delete in an old partition shows in the model when that partition is next refreshed.

Timing it on your own list

The quickest proof is three timed refreshes of the same report in Power BI Desktop, each from a clean start.

1. Time a full refresh of the list query with no policy, and note the row count.
2. Add the RangeStart and RangeEnd filter, record Query Diagnostics, and read whether the request carries the filter.
3. Point the same query at the SQL copy of the list, add the same filter on sp_created, and time a refresh of the two-day window.

Then publish both versions with the same policy and compare the refresh history in the service over a week. If the list version takes as long as the full refresh, the filter is not folding, whatever the policy dialog says.

Setting it up

The SQL side follows the same steps as any SharePoint to SQL Server replication, set out in seven steps from a SharePoint list to a SQL Server table. The Power BI use case walks through changing a report’s source from the list to SQL, and the SQL Server and Azure SQL pages describe each destination. If you are weighing a Power Query or Fabric dataflow route, our comparisons with Power Query and Fabric dataflows and mirroring set out what Microsoft documents for each.

SPSync reads SharePoint and never writes back, so SharePoint stays the system of record. It copies list items and library metadata, not file contents. Power BI still refreshes its own model; SPSync keeps the table under it current.