Problem
You might have received the below error when you use SharePoint Rest API to retrieve list items.
Code: Microsoft.SharePoint.SPQueryThrottledException Message: "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator."
In my case I have received the same error for the following queries :
https://Tenant.sharepoint.com/sites/MySite/_api/web/lists/getByTitle('MyList')/items?$select=*
https://Tenant.sharepoint.com/sites/MySite/_api/web/lists/getByTitle('MyList')/items
Cause
As error message clearly indicates, this problem occurs since the problematic list contains lookup columns more than the List View Lookup Threshold limit. In SharePoint On-premises, the List View Lookup Threshold default limit is 8 and it can be changed on the Resource Throttling page in Central Administration. In SharePoint Online, this limit is 12 lookup columns and increasing the List View Threshold is not supported in SharePoint Online.
What columns are classified as Lookup columns
The following column types are classified as lookup columns: (Source: List view Lookup threshold uncovered)
- Standard lookup columns
- Managed metadata (single and multi-value)
- People and group (single and multi-value)
- Workflow status
Created by (people and group) - Modified by (people and group)
Additionally following columns shows on list view also work as lookup columns : Name ( linked to Document) , Link (Edit to edit item) , Name ( linked to Document with edit menu), type ( icon linked to document).
Workaround
In Rest API, you can fix this problem by limiting the number columns to be returned using Select option. If you do not use the select option (or if you use select=*), then the rest query try to retrieve all fields, so you can limit the columns by providing only required columns in select option.
https://Tenant.sharepoint.com/sites/MySite/_api/web/lists/getByTitle('MyList')/items?$select=Title,Id
Note : Your select query should not include more than 12 (List View Lookup Threshold) lookup columns.
You can apply the same workaround if you face the issue in Microsoft Flow and PowerApps. For SharePoint views, you can just modify your view to list only required columns so that there will be no more than 12 lookup columns inside your view.