Fast, reliable Power BI on SharePoint data

Last updated 23 October 2026

Power BI against a SharePoint list is slow to refresh, times out on the connector, and every lookup column shows up as an ID, not the value people expect.

A Power BI report on a SharePoint list refreshes in seconds when the list is new. A year later, with tens of thousands of work orders in it, the same report takes minutes when it works at all. The report has not changed; the list has grown, and the connector’s cost grows with it.

Why the report slows down as the list grows

The SharePoint List connector reads through the same REST API the browser uses. It pages through every item and resolves lookup and person columns as it goes, which is cheap for a few hundred rows and expensive for tens of thousands. Lookup, person and choice columns arrive as internal IDs, so the model needs merge steps of its own just to show a client name. DirectQuery is not on offer, because the SharePoint Online list connector supports import only. Scheduled refresh in the Power BI service then adds SharePoint throttling, which is why a refresh that succeeds on a desktop can fail in the cloud. SharePoint’s list APIs were built to serve the list UI, and a BI tool pulling the whole list on a timer is a different workload. Our post on what breaks past 5,000 items covers the threshold errors a report meets as the list grows.

Point Power BI at a table

SPSync copies the list into a database table and refreshes it with delta queries on a schedule. Power BI stops talking to SharePoint. It connects to SQL Server, Postgres, Azure SQL or a warehouse with that engine’s own connector, by import for a scheduled model or by DirectQuery when the report should be closer to live. After the first full load, each run moves only the items that changed.

The model you end up with

Each list is a table keyed on the SharePoint item ID. A lookup arrives as a key column and a display value, so the client name is on the row and the client table is one join away. Multi-choice columns get child tables keyed back to the parent, so a measure can count tags in plain SQL; multi-lookup and multi-person values are still being verified on a live tenant. Person columns resolve against one shared sp_users table, so “assigned to” and “created by” join the same way in every report. Deleted items keep their row with sp_deleted_at set, and the report decides whether to show them. If the model should not join to sp_users itself, SPSync can generate a reporting view per list that adds person names and leaves out deleted rows.

Moving an existing report across

Start with the access you already have: delegated sign-in covers the sites you can see, and an admin can grant per-site Sites.Selected app permissions when the sync needs to run unattended. Choose the site and tick the lists the report reads, or the whole site. Pick the destination and let SPSync create the schema. A schedule of every few minutes up to hourly covers most dashboards. The last step happens in Power BI: swap the SharePoint List source for the destination tables and rebuild the relationships on the key columns, which is usually less work than the merge steps it retires.

Changing datasource from SharePoint list to SQL in Power BI

That is the exact title of the community thread people find when they reach this point, and the answer is shorter than the thread. The report, its measures and its visuals stay. Only the source steps of each query change.

1. Replicate the lists the report reads and let the first run finish, so the tables exist in SQL Server before Power BI looks for them.
2. In Power BI Desktop, choose Transform data, select the query for the list and open the Advanced Editor. The first steps call SharePoint.Tables with the site URL and then pick the list.
3. Replace those steps with a connection to the database and the replicated table, or the reporting view SPSync generated for it, for example Sql.Database("sql01", "Reporting"){[Schema = "dbo", Item = "sp_projects"]}[Data].
4. Delete the steps that expanded lookup records or merged another list to find a name. For a lookup, the id and the display value are already columns on the row. For a person column, join the users table or read the reporting view, which already has the name.
5. Where a column name in the table differs from the one the model used, rename it in the query, so the measures and visuals that refer to it keep working. Check the data types, then Close and Apply.
6. Publish, and enter the database credentials under Data source credentials in the semantic model settings. An on-premises SQL Server needs an on-premises data gateway; Azure SQL Database does not, as long as Power BI can reach it directly.

The swapped tables stay in Import mode, which is the right choice for most scheduled models. If you want DirectQuery, add the table again in DirectQuery mode and point the measures at it. Learn’s storage mode page notes that a DirectQuery table can be changed to Import and cannot be changed back, so decide before you build on it.

If the model has an incremental refresh policy, the swap is when the policy starts to save time, because a RangeStart filter after Sql.Database folds into the SQL sent to the server. Filter on sp_created and use sp_modified only to detect changes. Our post on why incremental refresh does nothing on a SharePoint list explains why the same policy saved nothing against the list.

For a worked example on SQL Server, including DirectQuery, read the SharePoint list to SQL Server and Power BI walkthrough. It covers the desktop app, SPSync in your Azure and the command line.

Who runs the sync

A report owner can keep their own refresh current from the desktop app. When the report matters to more people than its owner, move the sync somewhere that stays on, so the refresh keeps running with every laptop closed. If you version your data plumbing, drive the same replication from the command line on a server or in CI.

Limits

SPSync copies list and library metadata. It does not copy file bytes, and it never writes to SharePoint. BigQuery and Databricks are shipped destinations today; check the destination page for current status before you commit a model to one.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name power_bi_sharepoint_lists --prefix power_bi_sharepoint_lists --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name power_bi_sharepoint_lists --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: power_bi_sharepoint_lists
prefix: power_bi_sharepoint_lists
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: power_bi_sharepoint_lists
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