The 12 lookup column threshold in views, flows, Power Apps and Power BI

23 October 2026

Tags: Relational databasesScaleDesktopCommand line

A list that worked for two years starts throwing the same message everywhere at once: “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.” The list view shows it, a Power Automate flow that reads the list fails with it, the Power Apps form stops loading and the Power BI refresh fails overnight. Nobody changed a flow or an app. Someone added a Reviewer column to the list.

The answers on forums say “remove some lookup columns from the view”. That clears the view, and then the flow still fails, because the flow does not read the view. This post walks through what counts towards the limit, how each of the four tools hits it and what each one shows, the per-tool fixes, and a joinable copy of the list for reports that need more lookups than SharePoint will join. The figures behind it are on our SharePoint list limits cheat sheet.

The query cannot be completed because the number of lookup columns exceeds the threshold

SharePoint calls the limit the list view lookup threshold. Microsoft’s software boundaries and limits page puts it at 12 joins per query, where a join is any column “based on lookup, person/group, or workflow status”. The Power Query connector documentation quotes the same 12 join operations per query for SharePoint Online. Every tool in this post is one more way of building that query.

The wording you see depends on how the query was sent. In the browser and through the Power Platform connectors, Microsoft Q&A threads quote it as the short form:

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.

A REST call to _api/web/lists/getbytitle(...)/items gets a longer sentence ending “exceeds the lookup column threshold enforced by the administrator”. In SharePoint Online that administrator is Microsoft. The threshold is not a tenant setting you can raise. A support agent’s answer on that thread, now shown as anonymous, says that in their own test the threshold was 12 in SharePoint Online and could not be changed. It is a configurable threshold only on SharePoint Server, where a farm administrator owns it.

The limit applies per query. A list can have more than 12 lookup and person columns; what fails is a query that asks for more than 12 of them at once. The Server page says the threshold does not apply to single item operations. The trouble starts with anything that reads many items with many columns, which is every view, every Get items step and every report.

What counts towards the 12?

More than the columns you created as lookups. An answer from Microsoft’s support staff on Microsoft Q&A says the threshold “includes lookup columns and person columns”, and a moderator on another thread adds managed metadata fields. Together with the Server page, the list is:

• Lookup columns, single and multiple value.
• Person or group columns, single and multiple value.
• Managed metadata columns.
• Workflow status columns, including ones left behind by old SharePoint Designer workflows.
• Created By and Modified By, which are person columns on every list.
• Checked Out To on a document library, another person column.

The system columns are why the count looks wrong. A list with ten lookup and person columns of its own is already at 12 once a query includes Created By and Modified By. In the Q&A thread above, the asker counted 4 lookups and 8 person columns, and the 8 included Checked Out To, Created By and Modified By. The same support answer points to hidden lookup columns added “about flows and workflows” as a source of joins that are easy to miss.

The “Add additional columns from source list” option on a lookup column matters too. On the Q&A thread where a team’s tracker list hit the error, the Microsoft Community Moderator’s first suggestion was to untick the extra fields it adds and try again.

The lookup threshold in a list view

A list view fails when its own column set goes over 12 joins. The fix in the view itself is the one every forum answer gives: edit the view and take out lookup and person columns until the count is under 12. Index and filter settings make no difference here, because the threshold counts columns and not rows.

Two things make this harder than it sounds. The default view is shared, so trimming it changes what everyone sees, and the columns people miss are often the ones that made the list useful. And the view only fixes the view. A new form, a flow or a report does not use your view unless it is told to, so it builds its own query, often over every column the list has, and fails with the same message.

Why does Power Automate Get items fail with the lookup threshold?

The SharePoint connector’s Get items action does not read through a view unless you give it one. On a list with more than 12 lookup and person columns in total, its request can go over the cap even when no single view does. The flow run fails with the short form of the message, followed by a clientRequestId and serviceRequestId, as in this Q&A thread about a library used by both Power Apps and Power Automate.

Microsoft’s fix is a parameter on the action. The SharePoint connector reference lists Limit Columns by View on Get items, Get item, Create item, Update item and the “When an item is created or modified” trigger, with the description “Avoid column threshold issues by only using columns defined in a view”. To use it:

1. Create a view on the list that holds only the columns the flow reads or writes, with no more than 12 lookup and person columns in it.
2. Open each SharePoint step in the flow, show its advanced parameters, and pick that view under Limit Columns by View.
3. Do the same on the trigger, which reads the item’s columns too and takes the same parameter.
4. Check any dynamic content that pointed at a column the view leaves out, because the step no longer returns that column.

That works until somebody edits the view. The view is now part of the flow, but nothing in SharePoint says so, and a list owner who tidies up views can break a flow they have never seen.

Power Apps and the lookup column threshold

A canvas app builds its own query from the columns it uses. Microsoft Learn’s page on efficient calculations says Explicit Column Selection “is enabled by default for all new apps” and “reduces the number of columns retrieved to only the ones that are used in the application”. With it on, an app that shows five columns asks SharePoint for those five.

The error appears in three cases. An older app may have Explicit Column Selection turned off, so it asks for every column and fails once the list has more than 12 lookup and person columns. A form with a card for every field asks for every lookup and person column on the list. And when a collection loses track of where a column came from, Explicit Column Selection drops that column, and switching the feature off to get it back brings every other column back too, the error with them. Learn’s fix is ShowColumns after the collection. The app shows the same message with a request id, and the same Q&A thread records a maker finding that ten lookup and person fields worked and an eleventh broke the app. Eleven plus Created By and Modified By is 13.

The fixes are to turn Explicit Column Selection on for the app, remove cards for lookup and person columns the screen does not need, and split a wide form across two screens bound to different column sets.

SharePoint lookup column limit in Power BI

Power BI meets the threshold through the SharePoint Online list connector. Microsoft Learn says the issue is limited to the connector’s 2.0 implementation, which “is subject to a maximum of 12 join operations per query” and fails “when more than 12 columns are accessed simultaneously”. The 2.0 connector offers two view modes. All includes every user and system column, so on a wide list it is the mode that fails. Default reads the list’s default view, and Learn’s workaround is “creating a default view with less than 12 lookup columns”.

The message reaches Power BI wrapped in a data source error. One Q&A thread from April 2026 shows the refresh failing after three lookup columns were added to a library:

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).

A 500 reads like a server fault, so the first reaction is often to retry the refresh or re-enter the credentials. Neither helps. The report needs either the Default view mode pointed at a narrower default view, or a source that is not a SharePoint query. Copilot Studio applies the same rule to list knowledge: it does not support a list with more than 12 lookup columns in the default view, and you cannot pick a different view as the source.

The other refresh failures on lists, throttling and incremental refresh that never folds, are in why Power BI refreshes on SharePoint lists slow down. The connector’s other 2.0 errors, and the choice between 1.0 and 2.0, are in SharePoint Online list 2.0 in Power BI.

Why hiding columns only moves the problem

Each fix above narrows one query, and each has a cost. The list ends up with a view per consumer: one for the flow, one for the app, a default view trimmed for Power BI and Copilot Studio. Each of those views is a dependency the list itself does not show, and the default view now has two jobs, showing people the list and feeding the report, that want different columns.

Some reports cannot be narrowed at all. A project register that joins a client, a programme, a site, a sponsor, a project manager, a finance contact and four approvers is already at eleven lookups before Created By and Modified By, and every one of them is a column the report exists to show. Splitting the list into two lists only moves the join somewhere SharePoint cannot do it.

How do you join more than 12 lookups?

Outside SharePoint, in a database. A SQL engine has no lookup threshold: a query joins as many tables as it needs. SPSync replicates each list into a table in SQL Server, Postgres or another database, and keeps it current with Graph delta reads every few minutes.

In the copy, a single lookup becomes two columns, {col}_id holding the target item’s id and {col}_value holding its display text. A person column becomes {col}_user_id, pointing at one shared sp_users table, and Created By and Modified By arrive as sp_author_id and sp_editor_id on every row. Multi-value lookups and multi-person columns get a child table each. The column type map has the full list, including which mappings have not been confirmed on a live tenant. Single lookups, the id and the display text, are confirmed; multi-value lookups and multi-person columns are not.

Reading the list does not trip the threshold either. SPSync’s reader never asks Graph for more than 12 lookup columns in one request. On a wider list it splits the column selection into batches, reads each one and merges the results by item id before writing the row. That batching has been tested against our Graph simulator only, and not on a live list with more than 12 lookup columns.

The register above, with every lookup resolved, is then one query:

SELECT r.title,
       c.title AS client,
       p.title AS programme,
       s.title AS site,
       pm.display_name AS project_manager,
       ed.display_name AS modified_by
FROM pmo_register AS r
LEFT JOIN pmo_clients    AS c  ON c.sp_item_id = r.client_id
LEFT JOIN pmo_programmes AS p  ON p.sp_item_id = r.programme_id
LEFT JOIN pmo_sites      AS s  ON s.sp_item_id = r.site_id
LEFT JOIN sp_users       AS pm ON pm.id = r.project_manager_user_id
LEFT JOIN sp_users       AS ed ON ed.id = r.sp_editor_id
WHERE r.sp_deleted_at IS NULL;

Add the approvers, the sponsor and the finance contact the same way. Power BI reads the table through the SQL Server or PostgreSQL connector, a flow reads it through the premium SQL Server connector, and the list’s own views go back to showing people what they need. If you turn on reporting views, SPSync generates one per list that already resolves person names and leaves out deleted rows.

The copy is one way. SharePoint stays the system of record, people keep editing the list, and the copy follows. It does not carry SharePoint item permissions, so give access to the database with the same care as the list.

Setting it up

1. In the desktop app, connect the site, tick the wide list and every list its lookups point at, and choose SQL Server or Postgres as the destination. From the command line, spsync export-config writes the same replication with --lists-by-name.
2. Turn on reporting views if the report should get person names without writing the joins.
3. Run the first sync. Later runs read only what changed, and the reconcile, weekly by default, checks the copy against the list.
4. Point the report, or the flow that only reads, at the table, and take the extra views off the list.

The cross-list joins page covers bringing both ends of every lookup into one database, and the SQL Server and PostgreSQL pages cover each destination.