SharePoint Online list 2.0 in Power BI: limits and errors

23 October 2026

Tags: Relational databasesReportingDesktopCommand line

The report worked for a year on the old connector. Then somebody rebuilt it on SharePoint Online list 2.0, because the dialog offers it and it sounded newer, and the refresh started failing on a list it had read happily the week before. Or the opposite happened: the 2.0 report was fine, someone added a Reviewer column to the list, and the morning refresh died with a 500 error nobody on the team recognises.

The forum answers come one error at a time. “Switch to 1.0.” “Remove some lookup columns.” “Make a view.” Each can be right, and each answer has a cost the thread does not mention. This post sets out what Microsoft Learn says changed between the two implementations, quotes the errors people actually see, and lists the options for each. Our other Power BI posts cover the general causes of slow and failing refreshes, incremental refresh on a list and the 12 lookup column threshold across four tools, so this one stays with the connector itself.

What changed between SharePoint Online list 1.0 and 2.0?

Microsoft’s SharePoint Online list connector page gives the history in four sentences. The 2.0 implementation arrived in the October 2020 release of Power BI Desktop, it includes “improved APIs and greater usability”, it “isn’t backwards compatible with usage of the 1.0 connector version”, and it has been out of preview since September 2022. Both versions sit behind the same SharePoint Online list entry in Get data, with an Implementation choice in the dialog.

The differences Learn documents are these:

Learn does not list the column-by-column differences in output, and it publishes no speed comparison. What it does say is enough to explain most broken refreshes after a switch: the two versions read different APIs and return different shapes, so steps written against one do not run against the other.

The M function behind both is SharePoint.Tables. Its Implementation option takes “2.0” or null, and null means the original implementation. ViewMode is valid only for 2.0, takes “All” or “Default”, and is “All” when you leave it out. A 2.0 query that names no view mode therefore asks for every user and system column on the list.

How to tell which implementation a report uses

Open Power Query, select the query, and open the Advanced Editor. The Source step shows the options:

Source = SharePoint.Tables(
    "https://contoso.sharepoint.com/sites/operations",
    [Implementation = "2.0", ViewMode = "Default"]
)

No Implementation option, or Implementation = null, means 1.0, often with ApiVersion = 15 in the same record. Implementation = "2.0" with no ViewMode means All mode. This is the first thing to check on any error below, because a report built by someone else may use either, and two queries in the same file can differ.

SharePoint Online list 2.0 too many columns: the join limit

This is the most common 2.0 failure, and Learn names it. The 2.0 connector “uses a different API than the v1.0 connector and, as such, is subject to a maximum of 12 join operations per query”. Queries fail “when more than 12 columns are accessed simultaneously”, and Learn says the issue is limited to 2.0. Every lookup column is a join, and so is every person or group column, which is where people get caught: Created By and Modified By are person columns, and a Microsoft answer on Microsoft Q&A confirms that person columns count. In All mode they are always in the query.

The refresh error carries SharePoint’s own sentence inside Power BI’s wrapper. From a Q&A thread in April 2026:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold).

Learn’s workaround is a default view with fewer than 12 lookup columns, read in Default mode. In practice:

1. In SharePoint, create a view holding only the columns the report uses, with no more than 11 lookup and person columns between them, Created By and Modified By included.
2. Make it the list’s default view. Default mode follows whichever view is the default, so this is the step that matters.
3. In the Source step, set ViewMode = "Default".
4. Refresh the preview, then fix any later step that named a column the view leaves out.

That view now belongs to the report as much as to the list. A list owner who adds a column to the default view, or makes a different view the default, changes what Power BI receives on the next refresh. Our lookup threshold post covers what counts towards 12 and why the same list can fail in flows and Power Apps for different reasons. Switching the query back to 1.0 also clears this error, since the join note does not apply to it, at the cost of the rebuild described further down.

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

SharePoint refuses any single query that would have to scan more than 5,000 items to answer. Microsoft’s troubleshooting article gives the browser’s version of the message (“The number of items in this list exceeds the list view threshold, which is 5000 items”) and says the threshold is set at 5,000 items by default in SharePoint Online. Through an API the same refusal comes back as:

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

One Q&A thread titled “Power BI connected to SharePoint Online list with 6000+ rows, but faces 5000 row import (Threshold) limit” quotes exactly that, wrapped in a 502 BadGateway with Microsoft.SharePoint.SPQueryThrottledException. Read the stack trace before you copy the fix from it: it names the ApiHubsRuntime API, which is the Power Platform connector runtime, which suggests the failure came through a Power Platform connection to the list rather than the Power Query connector in Desktop. The SharePoint refusal underneath is the same one.

A plain read of a large list through the Power Query connector pages through it and usually finishes, slowly. The threshold appears when SharePoint is asked to filter or sort first, and a view that filters on Status or sorts on Due date with no index on either column is the usual cause. On 2.0 that matters in Default mode, which takes its columns from the list’s default view. Learn describes Default mode only in terms of the view’s columns, and does not say whether the view’s filter and sort travel with it. So test it the simple way: open the default view in the browser. If the browser shows the threshold message, fix the view before you point Power BI at it, and if it loads, the view is not your problem.

The options, cheapest first:

• Index the column the default view filters on, and put that filter first, so SharePoint can cut the set below 5,000 before it sorts.
• Use a default view with no filter, sort or grouping for Power BI’s sake, and do the filtering in Power Query.
• Use All mode, if the list is narrow enough to stay under 12 joins.

Our post on what breaks past 5,000 items explains where indexes stop helping on a growing list.

Why does 2.0 return only part of the list?

A quieter failure than an error, and worse, because the report looks fine. One Q&A thread describes a library where 1.0 returned about 13,000 rows, every folder and file, but left out the Content Type column the report needed. 2.0 returned every column but only 172 of the 203 items in the library’s view. The asker could not get both from either version, and the thread ends without a Microsoft fix.

If the row count on 2.0 is short, compare it with the item count in the list settings, then work through the likely causes. A query in Default mode may be reading a view with a filter or an item limit. A library with folders may be returned differently by the two versions, as in that thread. And the default view may have changed since the report was built. Count rows in Power Query after every refresh that matters, because nothing else warns you.

Expression.Error: The columns parameter must be null

This one appears in the Navigator preview, before any of your steps run. From a Q&A thread where one list out of twelve failed after columns were removed and retyped:

Expression.Error: The columns parameter must be null, specify the number of columns, specify a list of column names, or specify a table type. Details: [List]

The answer on the thread points at the implementation: the connector may be on 2.0, and switching to 1.0 is the thing to try. The asker’s own test supports a schema cause, since a fresh list with the same columns imported cleanly and a copy of the broken one did not. Before switching versions, try the cheaper route. Create a new list view, make it the default, read it in Default mode, and see whether the preview loads. If it does, a column in the old shape is the problem and the rest of the report can stay on 2.0.

The AttachmentFiles column is missing on 2.0

Reports that open item attachments from a table visual used to expand the AttachmentFiles column. On 2.0 it is not there. A Q&A answer from an independent advisor says the v2 implementation exposes only the yes/no Attachments flag. The workaround suggested is a second query with the SharePoint Folder connector, filtered to the list’s Lists/<list name>/Attachments folder, where each subfolder is named after an item ID, then a relationship on that ID. Learn does not document the change itself, so treat this as the field report it is and check your own list.

Should you switch a working 1.0 report to 2.0?

Usually not, if it works. Learn gives no reason to move a 1.0 report that refreshes reliably: 1.0 is still in the dialog, and the join limit is a 2.0 note. Switching means rewriting every step after Source, because column names and shapes differ, and then checking every visual.

There are reasons to move. 2.0’s Default mode lets the list owner control which columns reach Power BI, which is useful on a list with dozens of columns the report never uses. And 1.0 has its own quirk in Learn’s troubleshooting section: it returns dates in UTC and Power BI does not convert them, so a 1.0 model needs a time zone step such as DateTimeZone.ToLocal on every date column.

For a new report on a list with a handful of lookups, start on 2.0 in Default mode with a view built for the report. For a wide list, or one that will keep gaining columns, the connector choice matters less than where the data comes from.

Reading the list from a database copy

Every option above keeps the report tied to a SharePoint view and the connector’s limits. The alternative is to stop reading the list from Power BI at all. Fabric can mirror a SharePoint list into OneLake, and our comparison with Fabric dataflows and mirroring sets out what Microsoft documents for each route. For a report that should run on SQL Server or Azure SQL, SPSync copies the list into a database table and keeps it current through Microsoft Graph delta queries, every five minutes by default.

The connector problems go away because Power BI no longer uses the list connector. It reads a table with the SQL Server connector. Lookup and person columns arrive as id columns, and Created By and Modified By join to one shared sp_users table, so a report can join as many lookups as it needs in SQL. SPSync’s own reader never asks Graph for more than 12 lookup columns in one request; on a wider list it reads the columns in batches and merges them by item id before writing the row. There is no view to keep trimmed, and no implementation to pick.

SPSync copies list items and library metadata, not file contents, so the attachments workaround above still applies to the files themselves. It reads SharePoint and never writes back, so the list stays the system of record. The Power BI use case walks through moving a report’s source from the list to SQL, the SQL Server and Azure SQL pages describe each destination, and our Power Query comparison sets the connector and a database copy side by side.