Reporting past the 5,000-item threshold

Last updated 23 October 2026

A list grows past 5,000 items and views start throwing the list view threshold error, breaking filters, exports and reports that worked fine last quarter.

The project register passes 5,000 items in the last week of the quarter. On Monday the “Open by region” view shows an error, the Excel export stops partway, and the flow that emails overdue items fails on its “get all items” step. All three worked on Friday.

The attempted operation is prohibited because it exceeds the list view threshold

That is the wording a script, a CSOM call or PnP PowerShell gets back, as Microsoft Q&A threads such as this CSOM question quote it. In the browser the same refusal reads “The number of items in this list exceeds the list view threshold”, the title of Microsoft’s troubleshooting article. Both mean one thing.

SharePoint’s list view threshold stops any query that would scan or sort more than 5,000 items without an index to narrow it first. Views without an indexed filter stop rendering. Exports fail. Power Automate flows that loop over the whole list start to error. The limit is the same on every plan and every licence, because it protects the SQL store underneath SharePoint itself.

Index and filter first

Try this before copying anything out. Index the column each broken view filters on, put that filter first, and check that it alone brings the result under 5,000 items. A view for one team’s open items often comes back that way and stays working for years. It costs nothing and needs no new tools.

An index does nothing for a report that joins two lists, totals a column across the whole list, or sorts on something unindexed, and those are the reports a growing list needs. The usual next step is splitting one list into several by year or region, which fixes the views and breaks the reporting the list was kept for. If the threshold has you asking whether the list should be a list at all, our post on SharePoint as a database has a decision table and the routes from Dataverse to a SQL copy.

Exporting a list past 5,000 items

Export to Excel and Export to CSV work from the view you have open, so a view the threshold blocks cannot be exported either. For a one-off, export indexed slices that each stay under the threshold, one per year or per status, and stitch them together. A weekly export done that way becomes a chore, and that is usually the export a database copy should replace, with Excel connected to the table instead.

Power BI and Power Automate past 5,000 items

Power BI reads the list through its SharePoint connector, page by page, so the refresh tends to slow down rather than stop, until the lookups in the default view pass the connector’s join limit or the refresh outgrows its window. Power Automate’s Get items fails outright when its filter query names an unindexed column. Our post on what breaks past 5,000 items takes each of these in turn, with the index-first fixes and the error each one shows, and the list limits cheat sheet has the Microsoft source for every figure.

Report from a copy that has no ceiling

SPSync copies the list into a database table, once in full and then incrementally: each delta sync moves only what changed since the last run. A reconcile, weekly by default and also available on demand from the command line, checks that SharePoint and the table still match. None of these reads go through a SharePoint view. SPSync pages through the Graph API’s list and delta endpoints, which are built for bulk reads, so the 5,000-item threshold never applies to them. Every query that failed in the browser becomes SQL against a table with proper indexes.

If you are writing this kind of sync yourself, our post on Graph list item delta: paging, tokens and missed deletes covers paging, expired tokens and how deleted items reach the copy.

The whole list sits in one table with no row-count limit. Lookup and person columns arrive as key columns, multi-value columns as child tables. A new column on the source list appears on the next sync without anyone editing a mapping. Deleted items keep their row with sp_deleted_at set, so last quarter’s figures still add up, and SPSync can generate a reporting view per list that adds person names for dashboards.

Planning the first load

Sign in, choose the site that holds the oversized list, and select it along with any lists it looks up to. Postgres or SQL Server fits most lists; a warehouse fits the very large ones. SPSync creates the schema. The first run is a full load, which takes a while for a very large list but happens only once. SPSync is tested to 10,000 items on a live tenant and to 1,000,000 on a synthetic Graph. Schedule delta syncs after that and repoint the reports at the table.

Where that first load runs is a practical choice. The desktop app is enough for a single oversized list, as long as the machine stays awake until the load finishes. SPSync in your Azure, a managed application for your own subscription, is deployed with you on a call while the Marketplace listing is prepared. Once deployed it keeps the schedule running with no laptop involved. The CLI runs the same load unattended on a server or in CI, with predictable timing.

Load on SharePoint

A large copy should not slow SharePoint down for the people using it. SPSync honours Graph’s Retry-After responses and meters the resource units it consumes. To run a heavy first load at a quiet hour, schedule the command-line tool from your own scheduler.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name sharepoint_5000_item_limit --prefix sharepoint_5000_item_limit --destination-kind mssql --connection-string-env SPSYNC_DB_CONNECTION --dataset-name sharepoint_5000_item_limit --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: sharepoint_5000_item_limit
prefix: sharepoint_5000_item_limit
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: mssql
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: sharepoint_5000_item_limit
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist