Why Power BI refreshes on SharePoint lists slow down

The report refreshed in seconds when the list was new. A year and twenty thousand rows later it takes minutes in Power BI Desktop, longer in the Power BI service, and one Monday the scheduled refresh fails with an error that mentions credentials or a 429. Searching for the error usually ends in a Fabric Community thread with a reply that names one cause. In practice there are four, they stack on top of each other, and the order you check them in saves an afternoon.

The headings below are the questions people ask in those threads, answered from Microsoft Learn, with the page linked each time. The last two sections cover what changes when Power BI reads a replicated table in SQL Server or Postgres.

Four causes, and how long each one takes

Most slow or failing refreshes on a SharePoint list come down to one or more of these. The right-hand column is the clock that decides whether the refresh finishes.

Behind all four sit the Power BI service’s own limits. A refresh on shared capacity has to finish in under two hours, and in five on Premium. The Microsoft Entra ID token that Power BI uses for SharePoint Online expires in about an hour, so a refresh that runs past the hour can fail with a credentials error while the password is fine. Someone re-enters it, the next refresh runs just as long, and it fails the same way. A list past the 5,000-item list view threshold can also refuse a query outright, and our post on what breaks past 5,000 items quotes the exact error text and the index-first fixes to try before moving the data.

Why is the refresh fast in Desktop and slow in the Service?

This is the most common thread title, and the accepted reply usually says “throttling”. That is part of the answer. In Power BI Desktop the refresh runs when you press the button, on your own machine and under your own sign-in, often at a quiet time of day. In the Power BI service it runs from the credentials stored on the semantic model at the slot you picked, which for most teams is early on a weekday morning, the hour every other team picked too.

Microsoft’s guidance on SharePoint Online throttling says that throttling and slowdowns are more likely in peak hours, and that off-peak means nights and weekends in the time zone your tenant was set up in. The service also holds the refresh to limits Desktop does not. Learn’s own advice is to time the refresh in Desktop first, since a model that needs more than two hours there may not be refreshable on shared capacity at all.

Moving the schedule off the busy hours

Open the refresh history for the semantic model and set each run’s duration against its start time. If the slow runs bunch up in the working day, move the slot to a time before the office starts editing the list. Shared capacity gives you eight scheduled slots a day, and Premium, PPU or Fabric capacity gives you 48, so spend them where the list is quiet. A better slot buys time. It does not reduce the requests a refresh makes, and the list keeps growing underneath it.

Why does the refresh fail with 429?

HTTP 429 is SharePoint Online answering “Too many requests”. Learn’s throttling page describes it as the response an application gets when it has sent too many requests in a time window and exceeded a predetermined limit. SharePoint keeps those limits per user account and per application in each tenant, and it measures the application’s share in resource units, with a budget per minute and per day that grows with the tenant’s licence count. A refresh that pages through a long list with several lookups makes many requests in a short window. Every other report refreshing as the same account draws on the same limit, so three reports reading one list at 7am are three times the load.

What Retry-After means for a long refresh

Each 429 response carries a Retry-After header saying how long to wait. Learn warns that throttled requests still count towards the usage limits, so a caller that retries early gets throttled harder, and one that keeps going can be blocked outright, with Microsoft telling the tenant through the Message Center. Whatever time a refresh spends waiting comes out of its two-hour budget on shared capacity. The fixes that work cut the number of requests. Share one semantic model between the reports that read the same list, and drop the columns no visual uses.

Incremental refresh on a list reads every row anyway

Incremental refresh works by passing a date filter, built from the RangeStart and RangeEnd parameters, to the source so that only recent rows are read. Learn’s incremental refresh overview is plain about what happens when that filter cannot fold into the source query. The engine may apply the filter locally, which means retrieving all rows for the table from the source, and that “effectively defeats the purpose of incremental refresh”. Power BI Desktop shows a warning in the policy dialog when it cannot confirm folding.

The Fabric Community threads on SharePoint lists report the same result, a policy that saves nothing because the date filter never reaches SharePoint. Check your own model with Power Query Diagnostics before relying on that, because the answer depends on the connector and on every step in your query. The same overview gives the contrast case, a filter on a SQL Server table that folds because SQL Server supports folding, which is why incremental refresh against a replica reads only the new partition. Our post on why incremental refresh does nothing on a SharePoint list walks through the Query Diagnostics check, Dataflow Gen2’s own incremental refresh and the choice between Created and Modified as the filter column.

Why does a lookup column slow the refresh?

A lookup column stores a reference to an item in another list. To return the value, SharePoint joins the two lists for the query, and the SharePoint limits page counts person and group columns as joins too. Each join is extra work on SharePoint’s side for every page of results, so a list with six lookups slows down faster as it grows than a list of the same length with none. The model then adds its own cost. A lookup arrives as an id or a nested record, and the query needs a merge or an expand step before a visual can show a client name.

Trimming the default view

The SharePoint Online list connector page says the 2.0 connector is subject to a maximum of 12 join operations per query, and that queries fail when more columns than that are read at once. Microsoft’s workaround is a default view with fewer than 12 lookup columns, read through the connector’s Default option. The same trim helps speed on a list well under the limit. Take out the lookup and person columns the report never shows, and remember that Created By and Modified By are person columns, so they count as well. Then the view has to stay trimmed as people add columns to the list.

Should I use SharePoint Online List 1.0 or 2.0?

The 2.0 implementation arrived in the October 2020 release of Power BI Desktop and left preview in September 2022. Learn describes it as having improved APIs and better usability, and it is not backwards compatible with queries written against 1.0, so switching an existing report means rebuilding its queries. 2.0 offers two views of each list: All, with every user and system column, and Default, which follows the view set as default in SharePoint. If that default is a Calendar or Board view, SharePoint returns only the columns shown in it, even when you choose All.

The join limit applies to 2.0 only. 1.0 has a quirk of its own, which is that dates come back in UTC and Power BI does not convert them, so a model on 1.0 needs a time zone step. For a new report, 2.0 with a trimmed Default view is the sensible start. For a report on 1.0 that works, leave it, because Microsoft publishes no speed comparison between the two and the rebuild is real work. Time both on your own list before you move. Neither version changes the throttling or folding causes above. SharePoint Online list 2.0 in Power BI quotes the errors each version throws and what to do about each one.

Why is DirectQuery not supported on a SharePoint list?

The connector simply does not offer it. According to the Power BI data sources page, each connector’s article lists its capabilities, and if DirectQuery is not listed, it is not supported. For the SharePoint Online list connector, Site URL is the only capability listed, so a list can only be imported and the report is as current as its last refresh. By contrast, the SQL Server connector lists DirectQuery, which is why the threads asking this question usually end with someone suggesting the list be copied into a database.

When import is good enough

If the report is opened once a day, an import refresh at 6am is enough, and DirectQuery would only add query load to the source. DirectQuery earns its place when people expect a change in the list to show up in the report within minutes, or when the model has outgrown what an import refresh can load inside its time limit. On a replica you can have both. Use import for the large fact tables and DirectQuery on the reporting views SPSync generates, so a person or lookup arrives with its name already joined.

How do I refresh Power BI automatically when the list changes?

The usual answer is a Power Automate flow. The SharePoint connector’s When an item is created or modified trigger starts the flow, and the Power BI connector’s Refresh a dataset action calls the Power BI REST API to refresh the semantic model. It works on a list that changes a few times a day.

The flow trigger and the eight-refresh cap

On shared capacity, the limit of eight refreshes a day applies to refreshes triggered through the REST API as well as scheduled ones, according to the Power BI data refresh page. A list that is edited all morning uses up the day’s refreshes before lunch, and the flow then fails until midnight. Each of those refreshes also reads the whole list again, which brings back the throttling cause. Premium, PPU and Fabric capacity have no fixed number for API refreshes, though they are still governed by capacity resources.

With a replica, the flow is not needed. SPSync reads only what changed on a schedule, every five minutes by default, so a DirectQuery report on the replica shows an edit within a few minutes without any Power BI refresh at all. That is not real time, and a report that must show an edit the second it is saved still needs a live source.

Lookup and person columns in the replica

In the replica, every lookup, person or multi-value column that the connector hands back as a flattened string or an id becomes something you can query. A lookup is an id column you can join on, a multi-choice or multi-person column is a child table, and people resolve against one shared users table, so “assigned to” and “created by” join the same way on every list. The join limit stops applying because SQL Server and Postgres have no such limit on a query, and the merge steps in Power Query go away.

The first run reads the whole list once and later runs read only what changed. On our dev tenant, a full load of four lists to SQL Server, one of them past the 5,000-item list view threshold, read 10,303 items with 115 Graph calls in 1m 52s, and a zero-change delta round across three smaller lists took 6 s.

Setting it up

For SQL Server, the setup follows the same steps as any SharePoint-to-SQL-Server replication; see Seven steps from a SharePoint list to a SQL Server table. If a script or a scheduled flow does the copying today, What it costs to keep a SharePoint export script running weighs that setup against an automated one. Before you move a report, the Power BI on SharePoint performance checklist gives eleven checks for a slow or failing refresh, with the Microsoft limit behind each, and the Power BI use case walks through changing the report’s source from the list to SQL.

SQL Server and Postgres are two of many destinations. Others include warehouses, lakes, search indexes and vector stores, and the same lists can go to several of them, one replication each. If the destination you need is missing, let us know.