Choose SPSync when a list has passed the view threshold or several reports need the same data; choose Power Query direct for a small list and a report one person maintains.
Documentation reviewed
Where Power Query fits
- Nothing to install; it is already inside Excel and Power BI
- familiar to analysts who build reports this way already
- quick for a one-off pull or a small list
- no separate infrastructure or database needed
Where it breaks
- Hits SharePoint's 5,000-item list view threshold on larger lists
- refresh reads live from SharePoint every time, which is slow and scales poorly
- the newer (v2.0) connector is limited to 12 join operations per query
- no cross-list joins beyond that limit without a lot of manual query work
- a refresh produces a workbook or dataset, not a shared, queryable database copy
- keeps no delta history, change tracking or record of deletes
Feature by feature
| Feature | SPSync | Power Query in Excel or Power BI, direct |
|---|---|---|
| SharePoint Online support | Core product | Yes, via the SharePoint Online list connector |
| On-prem SharePoint support | On the roadmap | Yes, via an on-premises data gateway |
| Destinations | Relational, warehouse, lake and search/vector destinations: 20 destination engines (30+ named targets such as Azure SQL, MariaDB and Supabase), including SQL Server, Postgres, BigQuery, Databricks, ClickHouse, Parquet, Iceberg, pgvector and Azure AI Search; see /destinations/ | Excel workbooks and Power BI datasets only |
| Lookup normalisation | Lookups become joinable key columns in a database you run | Resolved at query time, not stored as a relational key |
| Multi-value columns | Child tables | Handled with M query transformations, per report |
| Deletes | Soft delete by default, hard delete with the command-line tool | Not tracked; a deleted item just disappears on next refresh |
| Delta sync | Every few minutes by default, plus a reconcile, weekly by default | Full read on every refresh, live from SharePoint |
| Schema evolution | New columns appear automatically; on SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB retyped columns get a _v2 column | Queries typically need updating for new or changed columns |
| Hosted option | SPSync in your Azure | Power BI Service refresh, still reading SharePoint live each time |
| Desktop app | macOS and Windows | Built into Excel and Power BI Desktop; availability differs by platform, see Microsoft's documentation |
| CLI / CI | Built-in CLI | None |
| Per-site least-privilege permissions | Delegated sign-in or app-only with Sites.Selected, bring-your-own-app | Credentials are set per data source in Excel, Power BI Desktop or the Power BI service |
| Pricing model | Licensed per installation and per target family, on annual terms, with Standard Support included; no public price list, quoted after a call | Included with Excel or Power BI licensing you already have |
For a quick pull into Excel, a small list, or a report someone needs today and will not maintain for long, connecting Power Query straight to SharePoint is the fastest option. It is already inside Excel and Power BI, and most analysts know how to shape the result with M queries. If the list is small and the report is disposable, that is often the right level of effort.
As the list grows
Problems arrive as the list, or the ambition, grows. SharePoint’s list view threshold applies at 5,000 items, and Power Query’s own SharePoint connectors run into it too. Every refresh reads live from SharePoint, so it is slow and puts repeated load on the source each time someone opens the report. The newer connector allows at most 12 join operations per query, and lookups resolve at query time with no stored relationship. Because a refresh produces a workbook or a dataset, there is no shared queryable copy and no record of what was deleted.
One copy for every report
SPSync replicates the lists into a database on a schedule, so every report reads the same current copy and SharePoint is no longer queried live. Paging around the 5,000-item threshold and Graph throttling are handled from the start, so a large list that stalls Power Query replicates normally. Lookups become key columns you can join on and multi-value columns become child tables, so a join across lists is a plain SQL join with no limit on how many you write.
The point to move is usually when a second report or a second person needs the same data that one workbook holds today. If that workbook is a list someone exports by hand every week, exporting a SharePoint list to Excel automatically compares Microsoft’s own routes, each with its published limit, with a workbook that refreshes from the database copy.
Repointing the reports
- List the SharePoint sources each Power Query report reads.
- Sign in to SharePoint in SPSync and select the lists those reports use.
- Choose a destination database (Power BI can use DirectQuery against it) and set the sync schedule.
- Change each report’s data source from the live SharePoint connector to the new database tables.
- Retire the direct SharePoint queries once the reports are validated against the new source.
SPSync as a Power Query alternative for SharePoint lists
With SPSync in place, Power Query still shapes the data, reading database tables in place of the live list. Refreshes stop paging through SharePoint, joins across lists become plain SQL joins, and every report reads the same current copy.
Other alternatives
- Fabric Dataflow Gen2: Power Query run in Fabric on your capacity, so several reports can share one refresh.
- Dataverse virtual tables: live list data inside Power Apps, aimed at app makers more than report builders.
- Coupler.io: refreshes Microsoft Lists into Excel or Power BI on a schedule, without a database of your own.
- CData Sync: a licensed replication platform that copies SharePoint into a database alongside many other sources.
Frequently asked questions
Why does my Power Query refresh fail on a large SharePoint list?
Every refresh reads the list live, so a big list means many pages of requests, and a filter on an unindexed column runs into SharePoint's 5,000-item list view threshold. SPSync pages through the list once and then reads only the changes, and Power Query reads the database copy.
Can Power BI use DirectQuery against the SPSync copy?
Yes. Once the list is in SQL Server, Azure SQL or PostgreSQL, Power BI can import from it or query it with DirectQuery, and the report no longer touches SharePoint at refresh time.
What happens to items deleted in SharePoint?
The row stays and its sp_deleted_at column is set, so a report can show deleted items or filter them out. To remove the row instead, set deletes: hard on that replication in the command-line tool's YAML config. Runs from the desktop app and from SPSync in your Azure always keep the row.
Desktop, Azure or command line
All three editions run the same engine, write the same tables and use the same licence key, so the choice comes down to who looks after the sync.
Desktop app
For macOS and Windows. Sign in with Microsoft, tick the lists, choose a database and sync from your own machine on the schedule you set. Delegated sign-in needs no admin unless your tenant requires admin consent for apps.
In your Azure
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 is always on, so the sync keeps running with every laptop closed.
Command line
Every replication is a YAML file you can review and commit. Run it from a server, a container or a CI pipeline, and re-run it safely any time.