The ask, correct a failing Power Automate cloud flow. For months, this flow ran daily, without issue, querying a growing SharePoint Online list, but now, the Get Items action is failing and throwing an error:
- “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.“
Did the number of lookup columns suddenly exceed a threshold? Fortunately, no, otherwise the flow would’ve had problems sooner. So, what changed? Well, the list’s total item count recently surpassed 5,000 items. Though well below Microsoft‘s service limit of 30 million items, larger lists like this need special attention. Are columns indexed? How many lookup columns is too many? Etc.
Fortunately, again, list columns were already indexed. Also, there are only a few lookup columns, well below even the SharePoint Server limit of 12. However, as it turns out, for Power Automate to query larger lists, the default list view cannot contain lookup columns. Note, the lookup columns don’t need to be deleted, just removed from the view, or new views could be created without the lookup columns. Either way, make the change, then specify the view without the lookup columns in the “Limit Columns by View” parameter of the Get Items action and problem solved:
Conclusion:
Relatively simple fix here. Still, this illustrates that as lists grow, they can become tricky to work with and again, require extra attention. Actions may fail one day after working perfectly fine for weeks, if not months. Keep an eye on these larger lists and optimize the cloud flows whenever possible.
“If Rosa Parks had taken a poll before she sat down in the bus in Montgomery, she’d still be standing.”
Mary Frances Berry
#BlackLivesMatter
console.log('Charles'); 