Flow: Working around the lookup column threshold limit

Microsoft Flow is a very powerful and useful tool. Flow provides reliable solutions for automating processes and supporting business solutions. Flow can synchronize, collect and create data and much more. When creating a Flow that is connected to a SharePoint List you might encounter one of the limits of Flow. In this blog post I will explain how to work with the lookup column threshold limit.

What is the lookup column threshold limit?

The lookup column threshold limit it the maximum number (12) of lookup columns you can collect using an action or trigger in Flow. When you go over this limit you will receive the following error.

“The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.”

The threshold limit is set by Microsoft and cannot be changed. The reason this limit exists is to make sure the performance of the Flow is up to standards. You can also encounter this limit in PowerBI, but this post only focus on Flow.

The Lookup columns are

  • Lookup
  • 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)

Working around the limit

When using an action to collect data from a SharePoint list you can limit the gathered columns by selecting a view in the action.

  • Add the Get Items action.
  • Click on Show advanced options.
  • Select the view with the required columns (less than 12 lookup columns).
  • For certain SharePoint triggers you can also do this.