Have you ever inherited a massive Enterprise Power BI model where the core fact tables have 80+ columns?
You ask the business stakeholders if they actually need all of them. The answer is almost always a panicked, „Yes, leave them alone, it might break a report!” So, the bloat stays.
But if you work with Power BI at scale, you know how the VertiPaq engine operates under the hood. It’s a columnar database. It thrives on narrow, vertical tables. Every unnecessary column loaded into memory—especially those with high cardinality—consumes precious RAM, balloons the .pbix file size, and drags down your scheduled refresh times.
The traditional consulting approach to fixing this is brutal: spend hours manually right-clicking columns, checking „View Dependencies,” or using search functions to scan through hundreds of DAX formulas. It’s tedious, unscalable, and highly prone to human error.
If we want to build scalable BI architectures, we need to stop operating like manual laborers and start thinking like engineers. We need to automate the detection.
The Logic: How to definitively find dead weight
To mathematically prove a column is useless and safe to delete, we need to extract the semantic model’s metadata (via the .pbip or .bim structure) and verify two hard conditions:
- The Relational Graph: Is this column used as a primary or foreign key in any active or inactive relationship?
- The DAX Codebase: Is the exact name of this column referenced anywhere inside the raw DAX code of your measures, calculated columns, or role-level security (RLS) filters?
If the answer to both questions is „No”, the column is a ghost. It is occupying RAM but contributing absolutely nothing to the analytical output.
Building the Algorithm
Once you parse your model’s metadata into a clean tabular format using Power Query, you can build an evaluation engine using DAX. The core concept relies on iterating over your columns and cross-referencing them against your relationships and measure definitions.
Conceptually, the evaluation looks something like this:
// Conceptual DAX Evaluation for Column Bloat
VAR _CurrentColumn = [ColumnName]
// 1. Check the Relational Graph
VAR _InRelationships =
// Logic to check if _CurrentColumn exists in the FromColumn or ToColumn
// of your parsed Relationships table
// 2. Check the Codebase
VAR _InMeasures =
// Logic to search for the _CurrentColumn text string
// within the concatenated DAX block of all your model's measures
RETURN
IF( NOT _InRelationships && NOT _InMeasures, "Flag for Deletion", "Keep" )By scripting this out, you completely remove the guesswork. You transition from saying, „I think we can delete this,” to saying, „The engine confirms this column is completely orphaned.”
Stop doing it manually
Building these parsers and DAX evaluation algorithms from scratch takes weeks of deep technical work. But standardizing your BI team’s development pipeline shouldn’t require reinventing the wheel every time you onboard a new project.
This exact „Bloat Detection” algorithm is one of the core features I built into ModelLens – an automated governance framework developed under theBItoolbox.
Instead of billing clients for hours of manual dependency checks, ModelLens scans your local .pbip architecture instantly. It evaluates the dual conditions, flags every unused column dragging down your VertiPaq engine, and generates client-ready PDF documentation proving exactly what needs to be removed.
Stop manually documenting and debugging. Let the engine do the heavy lifting.
You can check out how ModelLens automates semantic model audits here: https://thebitoolbox.gumroad.com/l/modellens