Forum Discussion
Lookup Column Threshold - Can't Connect to SharePoint Online List
Hi there,
I am trying to connect to a SharePoint Online list but am getting the following error:
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 enforced by the administrator.).....
I found a post suggesting to connect using OData, but I am getting a similar error there.
I haven't found any documentation regarding what this threshold is, or if the administrator can change that. Can anyone provide some guidance on this?
- Thanks Bob!
According to this article the limits apply to Power BI too
https://community.powerbi.com/t5/Desktop/Lookup-Column-Threshold/td-p/536456
However, saying that and as you see by the article it isn’t 100% clear. I would personally recommend raising this one to Microsoft Support to get a workaround or confirmation of the limit.
Hope that answers your question!
Best, Chris
4 Replies
- Hi Bob,
Per several articles the threshold is 12 which was raised from 8.
https://powerusers.microsoft.com/t5/General-Flow-Discussion/SharePoint-lookup-column-threshold-enforced-by-the-administrator/td-p/238839
https://powerusers.microsoft.com/t5/General-Discussion/SharePoint-too-many-lookup-columns-cause-query-failure-PowerApps/td-p/12211
https://natechamberlain.com/2018/09/04/how-to-increase-lookup-column-limit-in-sharepoint-lists/
This looks like a hard limit. This article does suggest you may be able to work around it by setting a view with 12 or less
https://blog.portiva.nl/2018/07/04/microsoft-flow-get-item-from-list-with-more-than-12-lookup-columns/
However, there is no getting around that limit in SharePoint Online.
Hope that answers your question
Best, ChrisHiChrisHoardMVP ,
Thanks for the reply. I'm afraid I left out a key component. I am able to access the list on the site itself through all of its views, but I get the error when I am trying to connect to the list with Power BI. I have tried connecting to both a SharePoint Online List and an OData Feed.
Are you aware of any limitations on the Power BI side that are preventing me from connecting?
- Thanks Bob!
According to this article the limits apply to Power BI too
https://community.powerbi.com/t5/Desktop/Lookup-Column-Threshold/td-p/536456
However, saying that and as you see by the article it isn’t 100% clear. I would personally recommend raising this one to Microsoft Support to get a workaround or confirmation of the limit.
Hope that answers your question!
Best, Chris
