Error retrieving data Query cannot complete number of lookup columns exceeds lookup threshold

(1) Share

Report

Posted on by CU29092301-0

Receiving an error message when trying to view a record via Power App. How would I be able to fix this. I now have two apps down because of this error message/issue.

Categories:

Error handling

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Suggested answer

Pstork1 69,545 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

I'm assuming that you are using SharePoint as the data source. The SharePoint connector has a hard limit on how many lookup columns can be included in a list when returning records. SharePoint itself has the same limitation, but in SharePoint the limit is applied to views, not the list itself. Unfortunately, Power Apps doesn't support the use of SharePoint views, so its always looking at the full list. There are only two workarounds that I know of.

1) Remove some of the lookup columns to get you below the limit. This would include regular lookup columns and things like People/Group columns.

2) You can use a Power Automate flow to retrieve/save data to the SharePoint list from the Power app. Flows support the use of SharePoint views to limit the number of columns returned. So you can use a view to limit the number of lookup columns, then return the data to a collection in the Power App.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Suggested answer

Vish WR 3,648 on at

Like (0)

Report
Copy link

Link copied!
@CU29092301-0

This error happens because your SharePoint list has too many lookup/person fields, and Power Apps retrieves them at the data source level, exceeding the lookup column limit (~12 per query).

By default, Power Apps:

Does NOT use custom SharePoint views
Often pulls the full record schema, not just what’s shown in the UI

So even if your gallery/form shows fewer fields, the query can still exceed the limit.

Reduce lookup/person columns in the SharePoint list

Use Power Automate (reliable workaround)

Flow can use a SharePoint view (≤12 lookup columns)

Return only required fields to Power Apps

Vishnu WR

LinkedIn - My Tech Space

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥

Was this reply helpful? Yes No
CU29092301-0 96 on at

Like (1)

Report
Copy link

Link copied!
Pstork1, THANK YOU for your response.

Yes, SharePoint list is the data source.

This list has 140 plus fields. I don't know how I would be able to change/eliminate any of the fields as the list is also connected to/dependent on many other Apps (which are failing). I did try to do a lightweight view and realized that would not work because as you indicated, Power Apps doesn't support the use of SharePoint views.

The list is the data created from the App, user goes into the App, fills out the form, those details populate to multiple different SharePoint lists which is then these lists become multiple data sources. There are a number of flows pulling data from these lists.

I have another app that is used just to see a user's specific items that they processed via the App completely stop working for the same reason, the query on the lookup exceeds threshold limit. As a workaround, I have them viewing their items directly in the SP list instead of the App. The team requested I add another lookup and I refused, I told them, we are maxed out on lookups.

This app has three collections, two showing 0 rows, one of them is showing 11 rows, this is the one with the main search on colSatusValues (Data Row Limit set to 2000)

The StartScreen - If(Param("view")="CC",ScreenMyCCs,Screen1)

The OnStart - Sets varView, Param("view"), Filters on email for HM.Email = User().Email, Clear Collect(colStatusValues, Collect(colStatusValues, and ClearColelct(colMyCCs, Sort(ID,SortOrder.Descending)

There are four search buttons that allow you to narrow the search (Status, ID, Job Title, Justification), this data loads to the app so you can see it and them if you want to review all the details for the record, you would just click the Open Request button

I have a total button that used to show the total number for the user and also, a button to open the specific request

I don't see anything major in the App Checker either; refreshed all the data sources (4); App saying two are not being used, but I think they should be being used, could be why collections aren't populating, although from what I can see, these are also coming from the main RTH list

I also didn't create this, so I'm sure there are some more fun things that are going to happen. I don't know what to do here.

Was this reply helpful? Yes No
Suggested answer

Pstork1 69,545 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Since it appears most of the actual work is being done using collections I would work to replace the code populating the collections with calls to a Power Automate Flow. Then you can leverage the views created in SharePoint to limit the number of lookup fields being returned. That is probably your only real option here.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Suggested answer

11manish 3,029 on at

Like (0)

Report
Copy link

Link copied!
The error occurs because:

Your query includes too many lookup-type columns exceeding SharePoint limit

Fix:

Reduce lookup columns in app/view

Use ShowColumns() to limit fields

Load data in smaller chunks

Was this reply helpful? Yes No
CU29092301-0 96 on at

Like (1)

Report
Copy link

Link copied!

Thank you.

Reduced lookup columns, still bringing back 0 on CountRows(colMyRTH)

Was this reply helpful? Yes No
Pstork1 69,545 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Are you still getting the error? ShowColumns is applied after you connect to the list so that won't solve the problem of too many lookup columns. Did you actually delete the columns from the list and refresh the Data Source collection in the app? Also, using User().Email in the filter statement is non-delegable so it will only filter on the first 2,000 rows. To make it delegable store User().Email in a variable and use the variable in the filter statement to make it delegable.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No