Just Because Power Query Can Doesn’t Mean It Should
Power Query is incredible.
It’s one of the most powerful, flexible, and misunderstood tools in the Power BI ecosystem. It can join data, reshape it, cleanse it, deduplicate it, apply business logic, detect types, generate calendars, and perform transformations that rival full ETL pipelines.
And yet — the goal should be to use as little of it as possible.
If you open a Power BI model and see dozens of Power Query steps—or worse, complex chains of logic buried in M—it’s not impressive. It’s a red flag.
Power Query Is Not Your Data Platform
Let’s say the quiet part out loud:
Power Query is not where enterprise ETL belongs.
Especially when:
- Your data already lives in a data warehouse or lakehouse
- You have SQL, Spark, or dbt available
- The data is reused across multiple models, reports, or tools
If your organization has invested in a centralized analytics platform, then Power BI is a consumer, not a transformer.
Using Power Query as your primary ETL layer:
- Hides logic inside Power Bi files
- Breaks reuse across semantic models, data agents, etc.
- Encourages one-off transformations
- Makes testing, versioning, and governance harder
- Forces logic to run per model instead of once, centrally
That’s not architecture. That’s convenience.
The Ideal Power Query: Boring and Small
My ideal Power Query looks like this:
- Source
- Navigation
- Optional row limiting
- For development, performance testing, or incremental refresh
That’s it.
If I see:
- Merging queries
- Extensive column logic
- Conditional business rules
- De-duplication logic
- Type coercion across dozens of columns
- Complex step chains with unclear intent
…I immediately ask:
Why is this not in the source?
“But Power Query Can Do That”
Yes. It absolutely can.
Power Query is astonishingly capable. I am impressed every time I see what it can do.
But architecture isn’t about capability—it’s about placement of responsibility.
Just because Power Query can:
- Define business logic
- Shape conformed dimensions
- Apply semantic rules
- Resolve grain mismatches
Doesn’t mean it should.
Those steps belong where they can be:
- Tested
- Reused
- Documented
- Versioned
- Shared
And that place is:
- SQL views
- Warehouse tables
- Lakehouse tables
- dbt models
- Medallion layers (Bronze / Silver / Gold)
Not buried inside a Power BI file.
Reusability Is the North Star
Every transformation you put in Power Query is:
- Locked to that dataset
- Rebuilt for every refresh
- Invisible to other tools
- Harder to validate
When logic lives upstream:
- Multiple Power BI models can use it
- Other BI tools can consume it
- Data science and ML can rely on it
- Governance becomes possible
If you care about scale, AI readiness, and enterprise analytics, reuse is non‑negotiable.
Complex DAX Isn’t a Flex Either
This philosophy doesn’t stop at Power Query.
I’m equally unimpressed by:
- Overly complex DAX
- Measures compensating for poorly shaped data
- Logic that exists only because the model wasn’t designed correctly
Good semantic models make DAX simple.
If you need heroic DAX to calculate something basic, that’s often a modeling or upstream data problem—not a DAX skill problem.
Power BI’s Job Is Modeling and Experience
Power BI shines when it focuses on:
- Semantic modeling
- Measures
- Relationships
- Calculation groups
- UX and storytelling
When Power BI does everything, it becomes:
- Slower
- Harder to govern
- Harder to scale
- More fragile
When Power BI does what it does best, it becomes:
- Fast
- Trusted
- Reusable
- AI‑ready
The Discipline Is the Point
Using minimal Power Query steps isn’t a limitation—it’s discipline.
It forces you to ask:
- Is this logic reusable?
- Does this belong in the source?
- Should this logic exist once, centrally?
- Is Power BI the right layer for this responsibility?
Power Query is a phenomenal tool.
But great architecture is knowing when not to use it.
If your Power Query pane is boring, simple, and almost empty—
you’re probably doing something right.
Power BI with Me 