How to Identify Lookup Columns in SharePoint Lists (Without Breaking Your Power App)
Introduction
If you’ve ever seen a previously working Power App or Power Automate flow suddenly fail with a vague “Network error” or a message like:
The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold
…and you didn’t change a single line of code, you’re not alone.
This issue is not a Power Apps bug, not a Flow issue, and usually not something you “broke”. It’s almost always caused by the structure of the connected SharePoint list, specifically lookup‑type columns.
This post shows you, step by step, how to identify lookup columns in a SharePoint list safely, even if the app is inherited and undocumented, without breaking anything.
The Key Concept: What Is a “Lookup Column” (Really)?
In SharePoint, lookup does not mean only columns of type Lookup.
SharePoint treats several column types as lookup‑type columns because they internally reference external data.
✅ Columns that count toward the lookup threshold
All of the following count:
- Lookup columns (against another list)
- Person or Group columns
- Managed Metadata columns
- Enterprise Keywords
- Created By (system column)
- Modified By (system column)
- Calculated columns that reference any of the above
⚠️ Important: Each additional field pulled from a lookup also counts as its own lookup column.
💡 Quick Note on Enterprise Keywords: If you enable the Enterprise Keywords feature on your list, it adds a single column that pulls from the global Term Store. While powerful for tagging, keep in mind that it adds one (1) additional lookup-type column to your total count.
Why This Breaks Apps and Flows Suddenly
SharePoint has a hard limit of 12 lookup‑type columns per query. This limit applies regardless of the number of list items and cannot be increased.
Even if:
- Your app updates only one text field
- Your flow updates a single flag
- Nothing in Power Apps was changed
SharePoint still evaluates all lookup‑type columns behind the scenes.
If someone:
- Added a new Person column
- Added a Managed Metadata column
- Modified a Calculated column
…the total can cross the threshold, and updates start failing overnight.
Step‑by‑Step: How to Identify Lookup Columns (Safely)
You do not need Power Apps knowledge for this section.
✅ Step 1: Open List Settings (Read‑Only Action)
Go to:
- SharePoint site
- Open the list used by the app/flow
- Select Settings (⚙) → List settings
✅ This does not affect your app.
📸 Screenshot 1: SharePoint List Settings
✅ Step 2: Review Columns by Type
In List settings → Columns, review each column type, not just the name.
Create a simple count list.
How to Count Lookup‑Type Columns Correctly
Make a simple checklist like this:
| Column Type | Counts as Lookup? |
|---|---|
| Lookup | ✅ Yes |
| Person or Group | ✅ Yes |
| Managed Metadata | ✅ Yes |
| Created By | ✅ Yes |
| Modified By | ✅ Yes |
| Calculated (references lookup/person) | ✅ Yes |
| Single line of text | ❌ No |
| Choice | ❌ No |
| Number | ❌ No |
| Date/Time | ❌ No |
| Yes/No | ❌ No |
The moment the ✅ total exceeds 12, updates and queries can fail.
The Most Common “Hidden” Culprits
These are the columns people usually miss:
- Created By / Modified By (system lookups)
- Calculated columns referencing a Person field
- Managed Metadata added “just for reporting”
- Extra fields selected in a Lookup column’s More Options
 |
| 📸 Screenshot 3: Lookup Column “More Options” |
Shows how one lookup silently becomes 4–5 lookups
What NOT to Do (Important)
At this stage, do not:
- Rewrite Patch formulas
- Rewrite Flows
- Add indexes (this does not help)
- Add workarounds blindly in Power Apps
This problem lives in SharePoint schema, not low‑code logic.
🛠️ Technical Deep-Dive: Why do these count? You might wonder why Managed Metadata or Keywords count toward a "Lookup" threshold. Behind the scenes, SharePoint uses a hidden system list called the Taxonomy Hidden List (sometimes referred to in queries as TaxCatchAll). Every time you add a metadata field, SharePoint is technically performing a lookup to that hidden list to resolve the labels and IDs. This is why it consumes a "slot" in your 12-column limit.
Safe Ways to Fix the Problem
Once you identify the lookup columns, choose the least disruptive fix.
✅ Safest Options
- Remove unused lookup or Person columns
- Replace non‑critical Person/Lookup columns with text
- Move rarely used lookup data into a related list
- Reduce extra fields pulled by lookup columns
Why Inherited Apps Are Hit More Often
Inherited apps commonly:
- Use “just in case” metadata
- Accumulate Person columns over time
- Have undocumented calculated fields
The app itself may be perfectly fine. The SharePoint list is what drifted over time.
Final Checklist Before Touching Power Apps
Before opening Power Apps Studio:
- ✅ You counted all lookup‑type columns
- ✅ Total lookup columns ≤ 12
- ✅ Calculated columns reviewed
- ✅ No unnecessary Person or Metadata columns remain
Only then should you evaluate Power Apps or Flow changes.
Final Thoughts
If a Power App or Flow suddenly breaks without code changes, always audit the SharePoint list first.
Knowing how to identify lookup columns gives you back control and saves hours of unnecessary debugging.
This single step has solved more “mysterious network errors” than any formula rewrite ever will.
🚀 Architect’s Tip for Power Apps: If you are hitting the limit and cannot delete columns, try using Collections in Power Apps to load only the specific data you need at start-up, or use Power Automate to fetch the data and pass it back to the app as a JSON string. This bypasses the direct SharePoint query threshold for that specific view.
Post a Comment