By Hiran de Silva
There is a question I keep asking which, in the world of Excel, seems to be regarded as almost offensively stupid.
Someone presents a difficult Excel problem.
They have a report.
The report is in the wrong shape.
They need to turn it into a nice, clean, normalised table.
So everyone starts discussing how to transform it.
Power Query.
Unpivot.
Cleaning.
Splitting.
Combining.
Merging.
Perhaps a few clever formulas.
And eventually, after a considerable amount of work, the desired table appears.
And I ask:
Why did we need to do that?
That is the question I want to ask here.
The challenge isn’t the challenge
This was prompted by a recent Sunday challenge from Crispo, which concerned the sort of awkward reporting situation that appears regularly in organisations.
It reminded me of a well-known Excel example discussed by Bill Jelen in podcast 2316.
The scenario was roughly this: someone in an organisation receives spreadsheet reports from many different departments and people. They have to compile all of that information into a single report in a more useful, normalised tabular form.
The Excel challenge is obvious.
How do you transform all those different reports into the required structure?
And, yes, that can be an interesting Excel problem.
But that isn’t the interesting question.
The interesting question is:
Why does anybody have to do it?
I actually asked Bill Jelen that question.
His answer was essentially that this work needs to be done regularly.
And that makes the question even more important.
If someone has to perform this enormous exercise every week, month after month, year after year, then perhaps the problem isn’t that we haven’t found a clever enough way of doing it.
Perhaps the problem is that the work shouldn’t exist at all.
We have put the cart before the horse
Think about where the data originally came from.
If we’re talking about an ERP system, the data already exists in a structured database.
Accounting data is structured.
Payroll data is structured.
Personnel data is structured.
Manufacturing data is structured.
Supply-chain data is structured.
These systems are built around structured transactional data. The whole point of the underlying database is that the information is organised in a form from which different reports and outputs can be produced.
So we have a rather strange situation.
The organisation has spent an enormous amount of money installing an ERP system containing structured data.
Someone extracts a report from that system.
The report is shaped for human consumption.
The report is then distributed around the organisation.
Eventually somebody needs the underlying information in a different structure.
So they take the report and attempt to reconstruct the structured data that was already there in the first place.
That’s backwards.
It’s the cart before the horse.
The report is a derivation of the data.
It should not become the source from which we attempt to reconstruct the data.
Why are we normalising reports?
This is where the question becomes particularly uncomfortable for the Excel community.
A huge amount of Excel education is concerned with cleaning and reshaping data.
And much of that is extremely useful.
But we rarely stop to ask why the data arrived in that state.
Take one of the most commonly used Power Query operations: Unpivot.
Unpivot is enormously useful.
But why do we so frequently have to unpivot data?
Quite often, because somebody has given us a report.
And that report has been produced from structured data.
The original data wasn’t necessarily in that shape.
Somebody created a report because reports are designed to present information to people.
Then somebody else takes that report and says:
“I need this information in a normalised table.”
So we use Power Query to unpivot it.
And everyone congratulates themselves on the transformation.
But shouldn’t we be asking:
Why aren’t we getting the data from the original source?
Power Query is an ETL tool.
Extract.
Transform.
Load.
That’s what ETL is for.
It isn’t primarily a technology for repeatedly rescuing organisations from their own reporting architecture.
The Bill Jelen example
Go back to the example of the person receiving all those departmental spreadsheets.
The conventional Excel solution is to take all the different spreadsheets, work out where everything belongs, transform them and consolidate them into the required table.
But consider where the knowledge actually resides.
The person receiving the reports may have to spend an enormous amount of time figuring out what each item means and where it belongs.
But the people who produced the spreadsheets already know.
They own the data.
They know what the columns mean.
They know where the information came from.
They know what the categories mean.
They therefore have the knowledge required to put their data into the appropriate structure.
So why not change the architecture?
Instead of everyone emailing spreadsheets to one unfortunate person who spends a week assembling them, create a central table.
Each originating spreadsheet can upload its data into that table.
The people who own the data are responsible for ensuring that their data goes into the correct structure.
And suddenly the central table is already normalised.
There is no weekly reconstruction exercise.
The database doesn’t have to be complicated
This idea can sound far more sophisticated than it actually is.
You don’t necessarily need a gigantic enterprise database project to get started.
You can create an Access database in a matter of seconds.
You can create a table in it.
You can put that database somewhere central where the relevant Excel workbooks can reach it.
Excel can upload data into the table.
You can apply validation and checks before the data is accepted.
And Excel can retrieve the information again whenever it is required.
There is nothing particularly exotic about this.
The important distinction is that the table is not trapped inside somebody’s Excel workbook.
A table inside a workbook is inside a file.
It can be emailed.
Copied.
Renamed.
Lost.
Duplicated.
Overwritten.
A centrally located database table is different.
It becomes a place where the organisation’s data can reside.
The spreadsheet becomes a participant in the process rather than the place where the data permanently lives.
The Digital Librarian idea
This is the fundamental architectural change.
Instead of having twenty different spreadsheets containing fragments of information, with somebody subsequently trying to assemble them, we have a central repository.
The spreadsheets can put information into it.
Other spreadsheets can retrieve information from it.
The data is stored once.
It is structured.
It is accessible.
And, importantly, everyone knows where it is.
You could use Access.
You could use SQL Server.
You could use a cloud-hosted SQL Server.
The technology can scale according to the requirement.
The principle doesn’t change.
Put the data where it belongs, rather than repeatedly reconstructing it from reports.
And what about the report?
This is the really important part.
We still need the report.
Of course we do.
The answer isn’t to abolish reports.
The answer is to reverse the direction of travel.
Instead of:
Database → Report → Spreadsheet → Transformation → Normalised Table
we should be thinking:
Structured Data → Central Data Store → Report
If we need a particular report, generate it from the structured data.
If we need a different report, generate that too.
If another department needs a different shape, generate that.
The reporting layer can change without destroying the underlying data architecture.
That is the whole point of separating data from presentation.
ERP systems make this even more obvious
This becomes almost absurd when we are dealing with a large ERP system.
Imagine an organisation spending millions on an ERP implementation.
The ERP system contains structured transactional data.
Then somebody says:
“Unfortunately, the report doesn’t come out in exactly the form we need.”
So they export the report to Excel.
Then somebody transforms it.
Then another department sends another report.
Then someone consolidates the reports.
Then someone else cleans the data.
Then someone else unpivots it.
Then management asks for another report.
And eventually someone has to combine everything again.
Why?
The original structured data is still sitting there.
We have simply created a chain of increasingly complicated representations of it.
This is not a criticism of the Excel professional
There is an important distinction here.
If someone is hired to take twenty horrible reports and turn them into a clean table, and they use Power Query to do it brilliantly, that’s not stupid.
They are solving the problem they’ve been given.
The problem is with the architecture that created the requirement.
The Excel professional may be doing an excellent job.
The organisation may simply be asking the wrong person to solve the wrong problem.
And that distinction matters.
Because if you are the person who can automate the report transformation, you may be praised for saving a week of work every month.
But if you can redesign the process so that the week of work never needs to happen, you have created something much more valuable.
Automation isn’t always enough
There is a tendency to think that the answer to inefficient work is automation.
But automation of unnecessary work is still unnecessary work.
If someone spends a week manually transforming reports, then an Excel expert comes along and reduces the process to twenty minutes with Power Query, that’s an impressive improvement.
But the organisation still has a process whose purpose is to reconstruct information that already exists elsewhere.
The ultimate improvement isn’t:
“Let’s automate the reconstruction.”
It is:
“Let’s stop reconstructing it.”
That is a very different way of thinking.
From monthly ETL to near-real-time data
Once you start thinking this way, the possibilities become much larger.
You don’t even have to update the central data store manually.
An ETL process can extract information from the ERP system and load it into a departmental SQL Server database.
Perhaps it only needs to happen once a month.
Fine.
Perhaps it needs to happen overnight.
Fine.
Perhaps it needs to check for changes every few minutes.
That can be done too.
The appropriate frequency depends on the business requirement.
The important thing is that the organisation has established a data pipeline rather than a collection of people passing files around.
Once the data is in the central database, Excel becomes extremely powerful again.
Excel doesn’t have to be the database.
It can be the interface through which people interrogate, analyse, manipulate and present the data.
That’s a much more interesting role for Excel.
What Excel is actually good at
This is where I think the Excel conversation often goes wrong.
People sometimes treat Excel as though its principal purpose is to clean up whatever mess has been thrown at them.
But Excel is capable of much more than that.
Excel can:
- validate data;
- upload data;
- retrieve data;
- query databases;
- generate reports;
- analyse information;
- perform calculations;
- provide user interfaces;
- automate processes;
- and present the results.
It doesn’t have to own the underlying data.
In fact, there are many circumstances where it is much better if it doesn’t.
The career implication
There is also a professional lesson here.
If your Excel career consists primarily of becoming increasingly good at cleaning increasingly horrible spreadsheets, there is a ceiling to what that skill is worth.
You may become extremely efficient.
You may become the person everyone calls when the reports are a mess.
You may become the Power Query expert who can transform almost anything.
But there is another level.
That is the person who walks into the organisation and asks:
Why are we doing this at all?
That person doesn’t merely make the existing process faster.
They change the process.
They remove unnecessary work.
They create centralised data stores.
They establish ETL processes.
They connect Excel to databases.
They eliminate repetitive manual consolidation.
And suddenly the value being created is no longer measured by how quickly someone can manipulate a spreadsheet.
It is measured by how much unnecessary work has disappeared.
The question nobody asks
This is why I keep coming back to that apparently stupid question.
Someone says:
“Here’s a report. How do we normalise it?”
And my response is:
“Why do we need to normalise the report?”
Not because I don’t know how to do it.
Not because Power Query can’t do it.
Not because the Excel professional doing it is doing anything wrong.
But because the question may be exposing a much bigger architectural problem.
If the data already exists in normalised form somewhere else, why are we treating a report as though it were the source?
A report is an output.
The data is the source.
We should go to the source.
And then generate whatever outputs we need.
Stop normalising reports
So perhaps the next time someone presents an Excel challenge involving a horrible report and asks how to transform it into a nice normalised table, we should resist the temptation to reach immediately for Power Query.
Before asking “How do we do this?”, ask:
“Why do we need to do this?”
Perhaps the answer will genuinely be that the transformation is necessary.
Fine.
Do it.
But perhaps the answer will reveal something much more interesting.
Perhaps the data already exists in a structured form.
Perhaps the organisation already owns an ERP system containing exactly the information we need.
Perhaps the reports are merely derived presentations of that data.
Perhaps the real problem is that we are extracting the data from its structured source, turning it into reports, distributing those reports, and then paying people to reconstruct the structured data again.
If that’s what’s happening, then the cleverest Power Query solution in the world is still solving the wrong problem.
Don’t ask how to normalise the report.
Ask why the report needs to be normalised in the first place.
That question may be the beginning of a much better Excel architecture.
Add comment