By Hiran e Silva
In the world of enterprise budgeting and reporting, we often hear that Power Query is the modern way to consolidate data. But is it truly an upgrade? Or is it simply better than a flawed method—like external links and copy-paste chaos—without ever being compared to the real gold standard hiding in plain sight?
Let’s zoom in on a real case: 383 budget templates across four levels of organizational hierarchy. Sounds like a nightmare to consolidate, right? But as many Power Query fans have shown, it can be done. You place the files in a folder, Power Query scoops them all up, transforms the data into a tidy memory model inside Excel, and voila—a consolidation via pivot table. Fast. Clean. No links. No formulas. No drama.
Or so it seems.
The Price of the Power Query “Upgrade”
But wait. This solution comes with two significant regressions:
- It’s now a batch process.
Budget holders can’t just update their sheet and see the result flow instantly into the group report. They must send their file to someone else, who updates the folder, refreshes the query, and re-generates the output. - The output is a pivot table, not a proper report.
Pivot tables are great for data exploration, but not ideal as polished management outputs. You’ll likely need another round of formatting before distribution.
So, Power Query removes the fragile external links—but at the cost of agility and user experience. If it solves two problems but introduces two others, is that progress?
The Real Question: Where Is the Data Model?
Here’s the critical insight: Power Query works by loading data into an internal Excel data model. That’s the limitation.
Budget holders can’t “talk” to this internal model. They can’t upload their updates to it. They can’t retrieve live consolidated data from it. And unless you’re the admin with the master workbook, you’re locked out.
So the real limitation is not Power Query itself—it’s the decision to embed the data model inside one Excel workbook, rather than having it live centrally, accessible by all.
And that brings us to the methodology that quietly solves all of this…
Hub-and-Spoke: The Enterprise Solution That Already Exists
What if the data model didn’t live inside a spreadsheet at all? What if it lived in a central database, accessible to every spreadsheet in the system?
That’s exactly what Hub-and-Spoke architecture does.
In this setup:
- Each budget template is still a normal Excel file.
- The data model lives centrally in a relational database (e.g. Microsoft Access) on a shared drive.
- When a budget holder updates their sheet and clicks “Put”, their data is uploaded straight into the central model.
- Any consolidation workbook, anywhere in the organization, can pull fresh consolidated data at any level—on demand.
Let’s return to our example:
A manager in Chennai updates their Cool Bikes budget and clicks “Put.” A second later, a finance lead in London hits “Get” and instantly sees the new data reflected in their report.
No waiting. No batch refreshes. No admin bottlenecks. And—crucially—the report looks just like the budget template, not a pivot table.
That’s not just a consolidation. That’s an enterprise system built entirely in Excel and Access.
Same Engine, Different Outcome
Ironically, both systems—Power Query and Hub-and-Spoke—are powered by a data model with two tables: one for budgets, one for the group map. The key difference?
- In Power Query, the data model is embedded inside an Excel workbook.
- In Hub-and-Spoke, the data model is external, shared, and relational.
The first locks everyone out. The second invites everyone in.
One is isolated. The other is collaborative.
One is a personal workaround. The other is a scalable solution.
Why Has No One Made This Comparison?
Here’s the strangest part: despite Power Query being widely promoted for over a decade, no influencer, trainer, or expert has ever seriously compared it to the Hub-and-Spoke method.
Why?
Because they’re benchmarking Power Query against external links—not against what already replaced external links 30 years ago. Most Excel influencers simply didn’t get the memo.
As a result, the Power Query model is praised as a breakthrough, when in reality it’s a lateral move—fixing old problems but reintroducing others that were already solved.
The Librarian Analogy
Think of it this way: in the Power Query model, every budget holder must send their book to a central admin, who compiles the data and mails out summaries to 145 managers.
In the Hub-and-Spoke model, budget holders entrust their book to a librarian—a central database—who instantly answers any consolidation query from anyone, anytime.
Same data. Same reports. But vastly different workflow.
A Missed Opportunity with Real Costs
It’s not just a theoretical comparison. The opportunity cost is enormous. We’re talking about:
- Delayed decisions due to outdated consolidations.
- Admin overhead from repetitive refresh-and-format tasks.
- Frustration from users locked out of live data.
- Lost agility in negotiation and planning cycles.
All of this because the data model was put inside a file instead of outside it.
Final Word: If It’s Not Better, Why Push It?
Here’s the most important question: in any mature field, when a new tool or method is introduced, it is expected to prove its superiority over what came before.
So why hasn’t the Power Query community explained why embedding the data model is better than centralizing it?
They haven’t—because they can’t.
Because it isn’t.
And the truth is: Excel has had the solution all along. A live, scalable, fully formatted, distributed, real-time budgeting and consolidation system—using nothing but Excel, Access, and a bit of know-how.
It’s time we stop settling for new features that look modern but fail at the fundamentals.
It’s time we remember that Excel isn’t broken.
We’re just using it the wrong way.
Call to Action:
If you’re still doing consolidations with links, pivots, or manual refreshes—get in touch. The Hub-and-Spoke model has been waiting for you since 1995.
Add comment