Established onExcel 2016 and later: Power QueryExcel 365: same, more connectorsGoogle Sheets, web: IMPORT functions and connected data
Doing an import by hand produces data and no record of how. Doing it as a stored query produces the same data, a list of the steps, and the ability to repeat them.
What the manual route leaves behind
Nothing. The rows somebody deleted, the column they split, the type they set, the find and replace they ran are not written down anywhere, and next month the work is done again from memory by whoever is available.
Which is why two people importing the same export produce different sheets, and why nobody can answer the question of what was done to the data between the system and the report.
What the stored route provides
Excel's query tool records each transformation as a named step in a list. The steps can be read, reordered, edited and removed. The source is a parameter rather than a path typed once. Types are declared per column rather than guessed. And refreshing re-runs everything against new data without anybody repeating the work.
Google Sheets approaches it differently, through functions that pull data in and recalculate, and through a separate connected-data feature for large sources. The model is a formula returning a range rather than a stored sequence, which is simpler and offers less control over types.
The trap in the type step
Worth knowing before relying on it. The query tool infers types by examining a sample of the rows rather than all of them, and writes a step fixing those types. A column that is numeric throughout the sample and contains text further down produces errors in those rows once the type is fixed.
The errors are visible, which is better than the alternative, and the sample size is a setting. The remedy is to import as text and convert deliberately where the data is not uniform, which is slower and correct.
The other trap
The type step lists column names explicitly, so a source that renames a column breaks the query with a clear error. That is a feature, and it means a query is coupled to the exact column names of its source and will need editing whenever they change.
Which is the honest cost of the approach: a manual import absorbs a renamed column silently and a stored query stops. Stopping is better and it is not free.
What this changes about the whole workflow
An import that is a documented sequence is the difference between a spreadsheet and a small pipeline. The steps are the closest thing to source code that either product offers outside the formula language, and unlike formulas they are listed in one place in order.
It is also the single largest structural improvement available to most people reading this site, and it requires learning one tool rather than changing how anything else is done.
Where it does not reach
The query produces a table; everything downstream of that table is still ordinary formulas with all the properties described in the first part of this site. The import is now repeatable and the model built on it is not.
So the improvement is real and bounded, and the entry on when work should leave the spreadsheet is about the point where the bound matters.
What to write down anyway
The steps say what was done and not why. A step named removed rows does not record that those rows were a subtotal block the source system emits every twelfth line.
Rename the steps to say the reason, which both tools allow, or keep a cell on the sheet explaining the query. The first is better because it travels with the query.
What we cannot verify
The features, the step model and the sampling behaviour of type detection are documented by the vendor and were confirmed on a current build. The default sample size has changed between versions and the figure should be checked rather than remembered. Google Sheets connected-data capabilities vary by account type and were not tested across all of them.
Where the steps live and who can see them
The query is stored inside the workbook, so it travels with the file and anybody who opens it can read the steps. That is a genuine advantage over a script kept on somebody's machine, and it is also the reason a workbook can contain a connection to a database that a recipient did not expect to receive.
Before sending a workbook outside the organisation, look at what its queries point at. A connection string is documentation of your internal systems, written by you, travelling with a file you meant as a report.
In short
- A manual import leaves no record of what was done between the system and the report.
- A stored query lists each transformation as a named, editable, repeatable step.
- Types are declared per column rather than guessed, and refreshing repeats everything.
- Type detection samples rows rather than reading all of them, so late anomalies error.
- A renamed source column stops the query, which is better than absorbing it silently.
- The steps say what was done and not why, so rename them to carry the reason.