SOLVED

Lookup Column Threshold - Can't Connect to SharePoint Online List

%3CLINGO-SUB%20id%3D%22lingo-sub-668517%22%20slang%3D%22en-US%22%3ELookup%20Column%20Threshold%20-%20Can't%20Connect%20to%20SharePoint%20Online%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668517%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20connect%20to%20a%20SharePoint%20Online%20list%20but%20am%20getting%20the%20following%20error%3A%3C%2FP%3E%3CP%3EDataSource.Error%3A%20SharePoint%3A%20Request%20failed%3A%20The%20remote%20server%20returned%20an%20error%3A%20(500)%20Internal%20Server%20Error.%20(The%20query%20cannot%20be%20completed%20because%20the%20number%20of%20lookup%20columns%20it%20contains%20exceeds%20the%20lookup%20column%20threshold%20enforced%20by%20the%20administrator.).....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20a%20post%20suggesting%20to%20connect%20using%20OData%2C%20but%20I%20am%20getting%20a%20similar%20error%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20found%20any%20documentation%20regarding%20what%20this%20threshold%20is%2C%20or%20if%20the%20administrator%20can%20change%20that.%26nbsp%3B%20Can%20anyone%20provide%20some%20guidance%20on%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-668517%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-668573%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Column%20Threshold%20-%20Can't%20Connect%20to%20SharePoint%20Online%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668573%22%20slang%3D%22en-US%22%3EHi%20Bob%2C%3CBR%20%2F%3E%3CBR%20%2F%3EPer%20several%20articles%20the%20threshold%20is%2012%20which%20was%20raised%20from%208.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FGeneral-Flow-Discussion%2FSharePoint-lookup-column-threshold-enforced-by-the-administrator%2Ftd-p%2F238839%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FGeneral-Flow-Discussion%2FSharePoint-lookup-column-threshold-enforced-by-the-administrator%2Ftd-p%2F238839%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FGeneral-Discussion%2FSharePoint-too-many-lookup-columns-cause-query-failure-PowerApps%2Ftd-p%2F12211%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FGeneral-Discussion%2FSharePoint-too-many-lookup-columns-cause-query-failure-PowerApps%2Ftd-p%2F12211%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fnatechamberlain.com%2F2018%2F09%2F04%2Fhow-to-increase-lookup-column-limit-in-sharepoint-lists%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fnatechamberlain.com%2F2018%2F09%2F04%2Fhow-to-increase-lookup-column-limit-in-sharepoint-lists%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20looks%20like%20a%20hard%20limit.%20This%20article%20does%20suggest%20you%20may%20be%20able%20to%20work%20around%20it%20by%20setting%20a%20view%20with%2012%20or%20less%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fblog.portiva.nl%2F2018%2F07%2F04%2Fmicrosoft-flow-get-item-from-list-with-more-than-12-lookup-columns%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblog.portiva.nl%2F2018%2F07%2F04%2Fmicrosoft-flow-get-item-from-list-with-more-than-12-lookup-columns%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20there%20is%20no%20getting%20around%20that%20limit%20in%20SharePoint%20Online.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20that%20answers%20your%20question%3CBR%20%2F%3E%3CBR%20%2F%3EBest%2C%20Chris%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-668730%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Column%20Threshold%20-%20Can't%20Connect%20to%20SharePoint%20Online%20List%20from%20Power%20BI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668730%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F169605%22%20target%3D%22_blank%22%3E%40Christopher%20Hoard%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%26nbsp%3B%20I'm%20afraid%20I%20left%20out%20a%20key%20component.%26nbsp%3B%20I%20am%20able%20to%20access%20the%20list%20on%20the%20site%20itself%20through%20all%26nbsp%3Bof%26nbsp%3Bits%20views%2C%20but%20I%20get%20the%20error%20when%20I%20am%20trying%20to%20connect%20to%20the%20list%20with%20Power%20BI.%26nbsp%3B%20I%20have%20tried%26nbsp%3B%20connecting%20to%20both%20a%20SharePoint%20Online%20List%20and%20an%20OData%20Feed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20aware%20of%20any%20limitations%20on%20the%20Power%20BI%20side%20that%20are%20preventing%20me%20from%20connecting%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-670730%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Column%20Threshold%20-%20Can't%20Connect%20to%20SharePoint%20Online%20List%20from%20Power%20BI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-670730%22%20slang%3D%22en-US%22%3EThanks%20Bob!%3CBR%20%2F%3E%3CBR%20%2F%3EAccording%20to%20this%20article%20the%20limits%20apply%20to%20Power%20BI%20too%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FLookup-Column-Threshold%2Ftd-p%2F536456%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FLookup-Column-Threshold%2Ftd-p%2F536456%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20saying%20that%20and%20as%20you%20see%20by%20the%20article%20it%20isn%E2%80%99t%20100%25%20clear.%20I%20would%20personally%20recommend%20raising%20this%20one%20to%20Microsoft%20Support%20to%20get%20a%20workaround%20or%20confirmation%20of%20the%20limit.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20that%20answers%20your%20question!%3CBR%20%2F%3E%3CBR%20%2F%3EBest%2C%20Chris%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673692%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Column%20Threshold%20-%20Can't%20Connect%20to%20SharePoint%20Online%20List%20from%20Power%20BI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673692%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20responses%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F169605%22%20target%3D%22_blank%22%3E%40Christopher%20Hoard%3C%2FA%3E%26nbsp%3B%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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?

4 Replies
Highlighted
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-enfor...

https://powerusers.microsoft.com/t5/General-Discussion/SharePoint-too-many-lookup-columns-cause-quer...

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

However, there is no getting around that limit in SharePoint Online.

Hope that answers your question

Best, Chris
Highlighted

Hi@Christopher Hoard ,

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?

Highlighted
Best Response confirmed by BobAtkinson (Occasional Contributor)
Solution
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
Highlighted