Reporting and Power BI on SharePoint data
Getting SharePoint list data into a database so Power BI, Excel and Access report against a table and leave the list connector behind.
The report was quick when the list was new. Two years on, the list holds tens of thousands of rows, the scheduled refresh runs for minutes, and the analyst who owns it spends Monday morning finding out why it failed over the weekend.
Most of that time goes on the SharePoint list connector. It reads through the same REST API the browser uses, a page at a time, and resolves each lookup and person column as it goes, so refresh time grows with every row and every lookup you add. Joining two lists means a Power Query merge that runs again on each refresh. Once a list passes 5,000 items, a query that filters or sorts on a column without an index can fail, which is where the flows and small apps built around the same report start to struggle as well.
A copy of the list in SQL Server or Azure SQL changes what the report reads. Power BI connects to an indexed table, so an import refresh is one query against a database, and DirectQuery becomes practical. Each lookup arrives as two columns, the related item's id and its display value, so a visual can show the client name and still join on the id. On the SQL destinations SPSync also generates a reporting view for each list, which gives the model a stable shape to point at. Excel and Access can read the same tables.
Freshness is the trade-off. The report is as current as the last sync, which on the default five-minute schedule is a few minutes behind SharePoint. For month-end figures that is closer than an overnight refresh, though a dashboard that must show an edit the second it is saved still needs a live source. SPSync does not replace the Power BI service, the gateway or your data model. It changes the source the model reads, and the measures and visuals you built stay as they are.
If your SQL Server runs in Azure, the Managed Instance post covers the firewall and network steps it needs. For the approach in brief, without the step-by-step detail, read the Power BI use case.
Start here
- Why Power BI refreshes on SharePoint lists slow down
Why the refresh slows down comes first, because the fix only makes sense once the cause is clear.
- Seven steps from a SharePoint list to a SQL Server table
The replication itself is second, one step at a time in the desktop app and on the command line.
- SharePoint list to SQL Server for a faster Power BI report
Building the report with DirectQuery needs the tables in place, so it follows the replication.
- Claims data from SharePoint in Azure SQL or Managed Instance
Azure SQL and Managed Instance add firewall and VNet steps, which only matter if your database runs there.
All posts on this topic
- Export a SharePoint list to Excel automatically each morning
Export to Excel, a Power Automate flow and Power Query each get a list into a workbook. Where each one stops, and how a SQL copy keeps the workbook current.
- Why incremental refresh does nothing on a SharePoint list in Power BI
You set RangeStart, RangeEnd and a policy, and the refresh takes as long as before. Why the date filter never reaches the list, and a source where it does.
- SharePoint Online list 2.0 in Power BI: limits and errors
Switch the connector to 2.0 and a working report starts failing on lookups, missing columns or rows. What changed from 1.0, the exact errors, and your options.
- Tableau on SharePoint lists after the connector deprecation
Tableau retired its built-in SharePoint Lists connector. What the JDBC replacement does, why its extracts reread every row, and how a SQL copy helps.
- Claims data from SharePoint in Azure SQL or Managed Instance
An insurer's policies, claims and referrals replicated into Azure SQL Database or Managed Instance, with the firewall and VNet steps each one needs.
- Why Power BI refreshes on SharePoint lists slow down
The SharePoint List connector pages item by item, so refresh time climbs with every row and lookup. A replicated table puts Power BI back on database speed.
- Seven steps from a SharePoint list to a SQL Server table
Connect, review, pick, connect SQL Server, run, schedule and monitor: each step of a SharePoint list replication in the desktop app, Azure or the CLI.
- SharePoint list to SQL Server for a faster Power BI report
Projects, Tasks and Departments replicated from SharePoint into SQL Server on a schedule, then read by Power BI with DirectQuery. Video included.
Other topics on the blog
Frequently asked questions
Can DirectQuery work against replicated data?
It can. Once the list is a table, DirectQuery behaves as it does against any database: filtered queries, no per-row API calls. The SharePoint Online list connector does not support DirectQuery at all, at any list size; its connector page lists no DirectQuery capability, so a list read directly can only be imported.
Will the report still show lookup values?
Each lookup arrives as two columns, the related item's ID and its display value, so the report shows the client name directly and joins on the ID when it needs more of the related list.
How current is the data in the report?
As current as the last sync, which on the default five-minute schedule is within a few minutes of SharePoint. That is far closer than a refresh run overnight or by hand.