And There Is a Method to My Madness
I recently left a rather short comment on a post by Paul Barnhurst.
Power Query is dead.
Paul replied:
“Yes it is, and so is the human race.”
Fair enough.
True. My comment was deliberately flippant.
We have all seen those Excel Is Dead posts where, after an attention-grabbing headline, the author eventually explains that Excel isn’t actually dead at all.
So I thought I would play the same game.
Except there is a method to my madness.
Because I want to make a serious argument.
And if you work extensively with Power Query, teach Power Query, promote Power Query, or advise finance professionals about Power Query, I would particularly like you to consider it.
My argument is not that Power Query is a bad technology. Or that it is dead.
Quite the opposite.
My argument is that we need to understand what Power Query actually is, what problem it was designed to solve, and—most importantly—where the data should go at the end of the process.
Because that last question changes everything.
Power Query Is an ETL Tool
Let’s begin with something uncontroversial.
Power Query is an ETL tool.
ETL means:
Extract. Transform. Load.
These concepts are much older than Power Query.
Whenever data needs to move from one system or data store to another, we potentially need to:
Extract it from its source.
Transform it into the shape required—perhaps filtering, aggregating, pivoting, combining or otherwise restructuring it.
And then:
Load the result somewhere.
Enterprise technology has had powerful generic ETL tools for decades. SQL Server Integration Services—SSIS—is an obvious example.
Power Query occupies a rather different space.
It brings extraordinarily useful data acquisition and transformation capabilities within reach of the desktop Excel or Power BI user.
And some of those capabilities are remarkable.
Power Query can extract data from databases, files, folders, web sources and even PDFs.
That last one tells us something interesting about its intended audience.
An enterprise integration architect might not regard a PDF as a particularly desirable system of record.
But for the ordinary business user?
Absolutely.
The invoice arrived as a PDF.
The report arrived as a PDF.
The statement arrived as a PDF.
That’s the reality of desktop information processing today.
And Power Query is very good at dealing with that reality.
The Real Power of Power Query
I would argue that the great achievement of Power Query is not merely that it performs transformations.
Transformations aren’t new.
What Power Query does brilliantly is make sophisticated transformations accessible through a user interface that ordinary spreadsheet users can learn. And there is no shortage of learning resouces.
Real-world transformation is inherently complicated.
Tables need joining.
Columns need splitting.
Data types need changing.
Rows need filtering.
Datasets need appending.
Data needs grouping, pivoting and unpivoting.
Power Query provides a structured environment in which users can construct these processes without first becoming professional database programmers.
That is a tremendous achievement.
And judging by the enormous ecosystem of books, courses, YouTube channels, consultants, trainers and influencers devoted to Power Query, the Excel community agrees.
So…
Power Query isn’t dead.
Not yet.
Because we haven’t discussed the L. Yet.
The L Changes Everything
Remember:
Extract. Transform. Load.
Extract the data.
Fine.
Transform the data.
Fine.
But then comes the architectural question that I think the Excel community spends remarkably little time discussing:
Load it where?
And that is where my argument begins.
Because in the conventional Excel Power Query workflow, the destination is ultimately associated with the workbook—whether a worksheet table, the Data Model, or another workbook-centred destination.
For many purposes, that is absolutely correct.
Suppose Tim receives information from several sources.
He combines it.
Cleans it.
Transforms it.
Analyses it.
And the resulting workbook is the end of the process.
Nothing wrong with that whatsoever.
Power Query is doing precisely the kind of job it is exceptionally good at doing.
But now let me change one fact.
Suppose Tim’s workbook isn’t the end of the process.
Suppose Tom needs Tim’s results.
And Todd needs Tom’s results.
And Ted needs something produced by Todd.
And perhaps Tim’s manager needs the information.
And Finance needs it.
And Operations needs it.
And another system needs it.
Now we have a completely different problem.
Because Tim’s workbook isn’t really the destination at all.
It is merely one stage in a larger business process.
And that distinction is enormously important.
Welcome to the Brick Wall
What normally happens next?
Tim sends the workbook.
Tom receives it.
Tom performs his work.
Tom sends another workbook.
Somebody consolidates several workbooks.
Another person extracts information from that consolidation.
Perhaps somebody creates another workbook from that.
Soon we have spreadsheets travelling between people, departments, folders and systems.
And now something fascinating happens.
Almost every criticism ever made about spreadsheets suddenly appears.
Version control.
Governance.
Scalability.
Collaboration.
Auditability.
Consolidation.
Access rights.
Duplication.
Manual intervention.
Which file is the latest version?
And eventually somebody arrives to tell the CFO:
This is why spreadsheets don’t scale.
No.
This is why point-to-point workflow architecture doesn’t scale.
That is a very different proposition.
The Problem Isn’t Power Query
This distinction matters.
I am not criticising Power Query for failing to do something it wasn’t designed to do.
Quite the opposite.
Power Query may have done its job perfectly.
The architectural mistake occurs when we treat the workbook as the final destination for data that actually needs to continue travelling through an organisation.
Think again about ETL.
Extract.
Fine.
Transform.
Fine.
Load.
Where?
If the answer is:
Into Tim’s workbook.
…and Tim is the only person who ever needs that data, excellent.
But if the answer is:
Into Tim’s workbook, which Tim will then send to Tom, who will process it and send another workbook to somebody else…
…then I would challenge the architecture itself.
Because the workbook was never the real destination.
Move the L
Here is the alternative.
Don’t load the transformed organisational data into a workbook as its ultimate destination.
Load it into a central relational database hub.
A table.
Outside the workbook.
Independently accessible.
A place that authorised spreadsheets and other processes can remotely query and update.
Now something extraordinary happens to the architecture.
The lines disappear.
Tim doesn’t send his spreadsheet to Tom.
Tom doesn’t send his spreadsheet to Todd.
Todd doesn’t send his spreadsheet to Ted.
Their spreadsheets stay exactly where they are.
They become clients.
The data lives centrally.
Tim queries what he needs.
Tim performs the work appropriate to his spreadsheet.
Tim updates the hub.
Tom queries what he needs.
Tom performs his work.
Tom updates the hub.
Hundreds of spreadsheet applications can potentially participate in the same process without the spreadsheets themselves travelling anywhere.
That is a completely different architecture.
And Look What Disappears
Now reconsider those traditional spreadsheet criticisms.
Version control
There isn’t a collection of competing workbook versions containing competing copies of the organisational data.
The current data is centrally held.
Historical versions can be timestamped and retained properly.
Consolidation
Why consolidate hundreds of files if the underlying data is already consolidated centrally?
Collaboration
People collaborate through shared data rather than shared files.
Audit trail
Updates can be recorded centrally.
Who changed what?
When?
From where?
Access rights
Users can receive precisely the data appropriate to them.
Automation
Processes can run against the central data without somebody opening, attaching, emailing, copying or manually consolidating workbooks.
Reporting
Reports don’t necessarily need to be physically distributed.
The report can remain where it is and refresh against the centrally held data.
Suddenly many of the supposedly inherent problems of spreadsheets aren’t inherent spreadsheet problems at all.
They are consequences of where we decided to put the data.
Which Brings Me Back to Power Query
Paul, this is where my deliberately provocative statement becomes more serious.
Power Query is dead.
No, obviously it isn’t.
Millions of people use it.
There is a gigantic education and training ecosystem around it.
And for the jobs for which it is appropriate, it is an excellent technology.
But I would ask every serious Power Query professional one question:
When you load data into a workbook, is that workbook genuinely the end of the data journey?
If it is, carry on.
But if that workbook subsequently needs to be sent to somebody else because the data—or the results produced from that data—need to participate in another stage of a business process…
…then ask yourself whether the ETL process has actually finished.
I would argue that it hasn’t.
You have stopped too early.
The L is in the wrong place.
Ask Yourself This
Think about every spreadsheet you have worked with during your career.
How many were genuinely isolated, self-contained processes?
How many received data produced by somebody else?
How many produced information required by somebody else?
How many were emailed?
Uploaded?
Downloaded?
Copied?
Consolidated?
Reconciled?
Passed from one department to another?
If almost every important spreadsheet participates in some larger organisational workflow, then perhaps we have spent years optimising the wrong architecture.
We have become extraordinarily sophisticated at getting data into the workbook.
But perhaps the more important question was:
Why did the organisational data need to live in the workbook in the first place?
The Irony
Here is the part that I think will surprise many Excel users.
Moving the data out of the workbook does not mean moving away from Excel.
Quite the reverse.
Excel can become dramatically more useful when it operates as a client to centrally managed data.
The spreadsheet remains the extraordinary calculation, modelling, reporting and user-interface environment that made Excel indispensable in the first place.
But it no longer has to carry the additional burden of being the organisation’s database, distribution mechanism, collaboration mechanism, audit mechanism and version-control mechanism.
Those responsibilities belong somewhere else.
This is the distinction between the spreadsheet as a document and the spreadsheet as a client application.
And once you understand that distinction, a great many arguments about Excel suddenly look very different.
So, Is Power Query Dead?
No.
And yes.
If your requirement is:
Extract → Transform → Load into a workbook that genuinely represents the end of the process
Power Query is very much alive.
It is exceptionally useful.
But if your requirement is really:
Extract → Transform → Load → send workbook → receive workbook → process → send workbook → consolidate workbooks → reconcile workbooks → govern workbooks…
Then I suggest Power Query isn’t your fundamental problem.
Your architecture is.
And if the better architecture is:
Extract → Transform → Load into a centrally accessible relational data hub
…then the question becomes interesting.
What role should Power Query play?
Perhaps Power Query evolves to make that destination as natural to the desktop user as loading a table into Excel.
Perhaps another ETL mechanism is more appropriate.
Perhaps the transformation should happen elsewhere entirely.
Those are implementation questions.
The architectural principle comes first.
Paul Barnhurst: Here Is My Challenge
So Paul, my Power Query is dead comment wasn’t really about Power Query.
It was about something much bigger.
We have an enormous Excel education industry teaching people increasingly sophisticated ways to import, clean, transform and load data into workbooks.
My challenge is this:
Before teaching people how to perform the ETL, shouldn’t we first teach them to identify the correct destination for the L?
Because if the workbook isn’t genuinely the end of the process, then loading organisational data into that workbook may simply create the first link in another chain of spreadsheet distribution.
And once enough of those chains develop…
…we recreate the very spreadsheet chaos that the software industry subsequently uses as evidence against Excel.
That is the irony.
So perhaps Power Query isn’t dead.
Perhaps something else should be.
The assumption that every spreadsheet data process should end inside a spreadsheet.
Kill that assumption…
…and Excel becomes a very different proposition.
And perhaps then the question isn’t:
Is Power Query dead?
The much more uncomfortable question is:
How much of what the Excel community currently teaches about Power Query is solving problems created by an architecture we should never have built in the first place?
Add comment