Part of: Reporting and Power BI on SharePoint data

SharePoint list to SQL Server for a faster Power BI report

· Updated

Relational databasesReportingDesktopCommand line

SPSync keeps SQL Server tables in step with your SharePoint lists on a schedule. Lookups become key columns, multi-value columns become child tables and deleted items are kept with a flag. Power BI then reads those tables with DirectQuery, and the SharePoint List connector drops out of the report.

Watch the full video

Chapters are in the video's description.

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment.

Book a call Ask for a trial

The SharePoint List connector in Power BI pages through the REST API one item at a time, so a report that refreshed in seconds when the list was small takes minutes once it has grown. This walkthrough moves a reporting team’s Projects, Tasks and Departments lists into SQL Server with SPSync and points the same report at the tables with DirectQuery. The video above follows the same steps on the desktop app.

The symptoms will be familiar to anyone who has kept a report on a list for a year. Each lookup, person or choice column arrives as an id the report has to resolve. Once a list passes 5,000 items, a query that filters or sorts on a non-indexed column can fail (what breaks past 5,000 items has the detail). A Power Automate flow copying rows across holds until someone changes a lookup or a multi-value column. Data Factory or a scheduled copy pipeline can do the job, but its schema and mapping have to be maintained by hand, and plenty of teams fall back to a one-off export into SQL Server or a spreadsheet.

What the connector does that a replica avoids

SharePoint’s list APIs are designed for SharePoint pages. A BI tool that pulls a whole list on a timer is a poor fit for them. SPSync reads the list into SQL Server once, then keeps the table current with delta queries, so Power BI, Excel and report writers query SQL Server and leave SharePoint alone. SPSync builds the schema itself: lookups and people become key columns, multi-value columns become child tables, and each run after the first reads only the items that changed.

Installing SPSync and signing in

Install the desktop app, or SPSync in your Azure for a tenant-wide setup. Sign in with the account that already opens Projects, Tasks and Departments in SharePoint Online; the sign-in is delegated, so SPSync reaches those lists and nothing beyond what that account can open. The permissions wizard explains what Sites.Selected grants before an admin approves it, and SPSync never writes to SharePoint. On the command line, sign-in is a device code and the token sits in the OS keychain, so a scheduled job stores no password. The command-line walkthrough runs this scenario end to end from a terminal.

Lookups, choices and people in SQL Server

Each list becomes a table keyed on the SharePoint item id, which is how later runs find the row to update. A single-value lookup becomes a key column named after the lookup, such as department_id on sp_projects. A multi-value lookup or choice becomes a child table with one row per value per item; Assigned To on Tasks becomes sp_tasks__assigned_to. Person columns from all three lists resolve against one shared users table, so “assigned to” and “created by” join the same way.

When a SharePoint column changes type, for example a number field becoming text, SPSync adds a column for the new type and keeps the old values as they were. A column deleted in SharePoint stays in the table, so a visual built on it still renders the next morning.

Pointing Power BI at the tables

With the lists in SQL Server, Power BI connects as it would to any database: import for a scheduled model, or DirectQuery for something close to live. DirectQuery sends filtered queries to SQL Server, which the SharePoint List connector cannot do at any useful size. Because the department lookup is now a column, you define the relationship once in the model and the report joins projects to departments like any two tables. The merge step that resolved lookups at refresh time goes away.

A five-minute schedule

Set the interval to five minutes and each run reads only what changed, keeping the report a few minutes behind SharePoint at most. A full reconcile, every seven days by default, re-checks every item; you can also run one on demand as a QA check, or after an outage on your own server or in Microsoft 365. The desktop app and SPSync in your Azure both have a schedule picker. From the command line, spsync run does one run, and cron, a scheduled task or a CI pipeline sets the pace.

Edited and deleted items

An edited item is updated by the next delta run, without a reload. A deleted item is soft-deleted: the row stays with a flag, so a report can filter it out or keep it for an audit, and the reporting view SPSync can generate for each table already leaves it out.

The tabs under each step show the same setup in the desktop app, in SPSync in your Azure and on the command line.

Connect to SharePoint and sign in

What you see: The connect wizard asking for the reporting site URL and a delegated sign-in, then the permissions wizard's one-page pack.

Why it matters: The report's data source runs under the reporting team's own SharePoint rights, so IT approves one site and no tenant-wide app.

What it replaces: A gateway service account with read access across the whole tenant.

  1. Open the desktop app, choose Connect a site and paste the SharePoint site URL.

  2. Sign in with the account that already has access to the Projects, Tasks and Departments lists.

  3. Review the permissions wizard's one-page pack before approving Sites.Selected access.

Pick the lists and the SQL Server destination

What you see: Projects, Tasks and Departments ticked together, mapped onto a new SQL Server schema with a preview of every column and type.

Why it matters: Tasks and projects both point at Departments, so one replication of all three keeps every lookup resolvable.

What it replaces: A hand-written ETL script or a Power Automate flow per list, each one breaking separately when a column changes.

  1. Tick Projects, Tasks and Departments, then choose SQL Server as the destination and point it at the target database.

  2. Check the schema preview: every column, its type, and which columns are lookups before anything runs.

Run the first sync

What you see: 148 projects, 121 tasks and 34 departments written in a single run, with a delta run after that.

Why it matters: Later runs fetch only changed items, so the five-minute schedule costs a handful of Graph calls per run.

What it replaces: A scheduled Power BI refresh that times out against the SharePoint List connector.

  1. Click Run now and watch the per-list row counts arrive: 148 projects, 121 tasks, 34 departments.

  2. Set the schedule to five minutes so the three-list replication checks for changes twelve times an hour.

See the result: lookups, child tables and joins

What you see: sp_projects joined to sp_departments in a SQL query, and a Priority column added to sp_tasks automatically after a change in SharePoint.

Why it matters: Workload by department is a join and a count, which SQL Server answers in one query and a list view cannot.

What it replaces: An export to a spreadsheet every time a report needs a join SharePoint cannot answer on its own.

  1. Open the destination database and browse sp_projects, sp_tasks and sp_departments; each keeps the SharePoint item id as its key.

  2. Run a join between sp_projects and sp_departments on department_id, something no SharePoint list view answers directly.

Connect Power BI with DirectQuery

What you see: Power BI reading sp_projects straight from SQL Server with DirectQuery, joined to departments on department_id.

Why it matters: Filters and slicers become SQL that SQL Server runs, with no per-item API paging and no 5,000-item view threshold.

What it replaces: The SharePoint List connector's scheduled refresh, which times out or falls back to import once the list grows large.

  1. In Power BI Desktop, connect to the SQL Server database and choose DirectQuery for sp_projects.

  2. Build the report against the department column the same way you would against any other database table, and define the relationship once in the model.

Set it up

Signing in and connecting are covered in the SharePoint connection guide. For the database side, follow the SQL Server setup guide. If a connection test fails, see troubleshooting.

What you get

We'll set it up with you on the call: your use case, further walkthroughs or demos, and a trial in your own environment.

Book a call Ask for a trial

Frequently asked questions

Why replicate a SharePoint list to SQL Server instead of using the Power BI connector?

The SharePoint List connector pages through the REST API one item at a time and resolves lookups the same way, so refresh time grows with row count and stalls past the 5,000-item view threshold. SPSync keeps a SQL Server copy of the list on a schedule, and Power BI queries that table at database speed.

How do you pick the list, site and target SQL Server database?

Sign in with delegated access or an admin-granted Sites.Selected app permission, pick the site and tick the lists, then point SPSync at a SQL Server connection string. SPSync creates the destination schema automatically from there.

How do Assigned To and the department lookup look in SQL Server?

The department lookup becomes department_id, a key column pointing at sp_departments. Assigned To, a multi-value person column, becomes sp_tasks__assigned_to with one row per person per task, and every person resolves against one shared users table.

Does Power BI still see a task after it is deleted in SharePoint?

The row stays in SQL Server with a deleted flag, so a report can show or exclude it and the history is kept. Edits reach SQL Server on the next delta run, within one schedule interval.

Does this write anything back to SharePoint?

No. SPSync reads from SharePoint and writes only to the destination. Nothing is installed on the site.

What we covered

Full transcript
We are looking at a reporting team's SharePoint site: a Projects list, a Tasks list and a Departments list. Past 5,000 items a query that filters or sorts on a non-indexed column can fail, Power BI against the list times out on refresh and turns every lookup into an id, and the Power Automate flow copying rows across breaks the moment a lookup or a multi-value column changes. SPSync connects to the site directly, replicates the schema and keeps SQL Server current on a schedule instead. We sign in with delegated permissions, the same account already used in SharePoint, so SPSync only ever sees what that account can see. Nothing installs inside SharePoint and nothing is written back. The permissions wizard gives an admin a one-page pack: exactly what Sites.Selected grants, and why. We pick Projects, tick Tasks and Departments too, and point all three at the SQL Server database. One connection and one run now cover all three lists on a single schedule, instead of three to manage by hand. The schema preview lists every column, its type and which ones are lookups, before anything runs: Departments has 34 rows and a lookup back to itself for a parent department, and Tasks carries a Projects lookup plus a multi-value Assigned To column. Before the first run, the database has the tables and no rows. We trigger a run now instead of waiting for the schedule. The first run is a full extract, every run after that is a delta reading only what changed. 148 projects, 121 tasks and 34 departments arrive in that single run. SPSync backs off the moment Graph's throttling signals say to slow down, so one heavy run never starves the rest of the tenant's calls. Each list is now a table with the SharePoint item id as its key. Lookups became a real column you can join on, the multi-value Assigned To column became its own child table, sp_tasks__assigned_to, one row per person per task, and every person across all three lists resolves to the same row in a shared users table. Deleted items are kept with a flag, so a report keeps its history. Here is a project joined to its department, a real SQL join across two tables: sp_projects.department_id to sp_departments.sp_item_id. Against SharePoint directly that join is not possible; the list only stores the department as a lookup id, with no relational query surface to join it to Projects in one call. Add a Priority column to Tasks in SharePoint and the next run adds it to sp_tasks automatically, no migration script and no downtime; remove a column and SPSync keeps it in the table, so a report built on it keeps working. The same setup and run works from the command line: export-config writes the site, the lists and the destination to a file, doctor checks the Graph permissions and the destination before anything runs, and run does the replication with no browser open. Power BI connects to sp_projects with DirectQuery, the SQL Server connector, not the SharePoint List connector. There is no import to wait on and no 5,000-item view to page through, and the department lookup is a real column you can join on, so the report joins it like any other table and defines the relationship once in the model. The report now reads SQL Server directly with DirectQuery, and the same three tables also feed finance's cost-centre reporting and operations' workload view, on the same schedule. SPSync connects to many destinations: relational databases, warehouses and lakes, search and vector stores, with the full list on the destinations page. The same lists can go to several destinations, one replication each, and if the destination you need is missing, tell us.

Compared with

Book a call to talk through your use case, see further walkthroughs or demos, and set up a trial in your own environment. No obligation.

Book a call Ask for a trial