Power Query in Excel: What It Does and When to Use It
Power Query is the one Excel feature that changes the shape of recurring work. It replaces the monthly rebuild with a refresh — but only for the kind of task it was designed for.
Short answer
The mental model: you build a recipe, not a result
A formula produces a value. Power Query produces a sequence of steps that produces a value. Every action you take is recorded as a named step in the Applied Steps pane, and pressing Refresh replays the whole sequence against the current source.
| Formulas | Power Query | |
|---|---|---|
| You build | A value in a cell | A sequence of recorded steps |
| Next month | Copy the formula down; fix the ranges | Press Refresh |
| If the row count changes | Ranges must be right, or the result is wrong | Nothing to do |
| If a column is renamed in the source | The formula breaks silently | The step errors visibly |
| Where the work happens | In the worksheet grid | In a separate engine, outside the grid |
| Source safety | You may be tempted to edit the source | The query never writes back |
| Best at | Calculation, analysis, anything that must respond to a manual edit | Import, cleaning, reshaping, combining |
The six things it does that matter for finance work
1. Import from sources a worksheet cannot reach
A folder of files, a CSV in a fixed location, a workbook on a network path, a database table, a web page with a table on it. The data lands in a query rather than being pasted into cells, which means the import is repeatable.
2. Combine many files from one folder
Point a query at a folder and it reads every file in it. Files added later are picked up on Refresh, and the structure is defined once in a transform query. This is covered in detail in combining multiple Excel files.
3. Clean, without leaving helper columns behind
| Clean-up | What it removes |
|---|---|
| Trim | Leading and trailing spaces |
| Clean | Non-printing characters such as line breaks and tabs |
| Replace values | Anything, including a non-breaking space you paste in as the search value |
| Change type | Explicitly sets a column to text, whole number, decimal or date |
| Split column | Separates a combined field on a delimiter or by position |
| Remove duplicates | Removes rows that repeat across the selected columns |
The point is that these happen inside the query, so the source sheet is untouched and no helper columns accumulate in it. The list of defects these fix is in Excel data cleaning.
4. Unpivot — the feature most people have not met
A cross-tab report with months across the columns and accounts down the rows is human-readable and almost useless for analysis. Unpivot Columns turns it into one row per account per month, which is the shape every pivot table and every comparison needs.
| Before — as reported | Apr (₹) | May (₹) | Jun (₹) |
|---|---|---|---|
| Salaries | 4,20,000 | 4,20,000 | 4,35,000 |
| After — unpivoted | Month | Amount (₹) |
|---|---|---|
| Salaries | Apr | 4,20,000 |
| Salaries | May | 4,20,000 |
| Salaries | Jun | 4,35,000 |
5. Merge — including the join that finds missing records
A merge joins two queries on a key column. The join kind determines what comes back, and one of these is worth knowing by name.
| Join kind | Returns | Use it for |
|---|---|---|
| Inner | Only rows where the key exists in both | The matched set |
| Left Outer | All rows from the first, plus matches where they exist | Enriching a list with data from another |
| Left Anti | Only rows from the first that have no match in the second | Missing records, from one side |
| Right Anti | Only rows from the second that have no match in the first | Missing records, from the other side |
| Full Outer | All rows from both, matched where possible | A complete picture including both sides' exceptions |
Left Anti is the reconciliation join
6. Group and summarise
Group By produces a summarised table — one row per key with a sum, count, average or min and max. It is the query equivalent of a pivot table, with the difference that the result is a table you can merge against something else.
A worked sequence
A monthly sales export arrives as a cross-tab, with a title row on top, a total column on the right, amounts stored as text, and a state name column that contains four spellings of two states.
Import the file as a query
Remove the top rows and promote the header
Remove the total column and the total row
Set the column types explicitly
Clean the key columns
Unpivot the month columns
Map the state names
Load to a table
The Applied Steps pane is where you fix things
| Situation | What to do |
|---|---|
| A step errors with a red mark | Click it and read the message. The error names the column or step that failed |
| The source file moved or was renamed | Open the Source step and update the path — everything downstream recalculates |
| A column was renamed in the source | Find the step that referenced the old name and update it |
| Steps were applied in the wrong order | Delete the steps from the point of the mistake and redo them; the query stops at the deleted step rather than corrupting |
| A step needs a comment | Rename it. A step called “Removed total row” is documentation; a step called “Filtered Rows1” is not |
| The query is slow | Check whether an early step is doing anything expensive, and whether a type change is being applied before a filter that would have removed most of the rows |
Refreshing, and why it sometimes does not
- Refresh updates one query; Refresh All updates every query in the workbook. Order is handled by the dependencies between queries, so a merge refreshes after its sources.
- Queries do not refresh on their own. A workbook opened tomorrow shows the data as of the last refresh until somebody refreshes it. Agree who does that, and when.
- Privacy levels and credentials can block a refresh, particularly where a query reads from two different sources. If a refresh fails with a privacy message, that is the setting to look at.
- A file that is open in Excel can hold a lock that prevents a query from reading it. This is the most common reason a refresh fails for no visible reason.
- The source has to be reachable. A query built on a network path fails on a laptop that is not on the network, and a query built on a local path fails for everybody else.
When Power Query is the wrong tool
- A one-off task. Setting up a query for something you will do once takes longer than doing it. Use formulas.
- Formatting a report. Power Query produces data, not presentation. Number formats, colours, merged headings and print layouts are not its job — that is where a short macro earns its place.
- Anything requiring judgement per row. A query applies rules. It cannot decide whether a difference is material.
- A source with no stable shape. If every incoming file is laid out differently, you will spend your time editing the query. Fix the source, or use a tool built to handle variable input.
- Very small data. Twenty rows pasted into a sheet do not need a query, and the query would be harder for the next person to understand.
Where it hands over
Power Query is excellent at getting two sets of records into a clean, consistent shape. It is less good at the decision that follows — whether a record that did not match on the exact key should match on a one-rupee tolerance, whether one payment clearing four invoices is a single group, and what the reason was for accepting a particular difference.
That is the point at which the problem stops being data preparation and becomes matching, and it is where a dedicated tool takes over. The Piloteq Automate workflow page describes what it does with the two prepared sets. And if you have not yet used Power Query at all, start with the merge types above — the Left Anti join alone will change how you do comparisons.
If you would rather not build this by hand
For the matching step, once the data is prepared
Piloteq Automate is not a data preparation tool. It takes two prepared sets of records, applies the match rules you set — exact, tolerance, last-digit, group — and returns the matched, unmatched and changed records with the reason recorded.
- ✓Group matching for one payment across many invoices
- ✓Amount tolerance and date tolerance you set
- ✓Duplicate detection across both files
- ✓Results as an exportable difference list
Frequently asked questions
What is Power Query in Excel?+
Power Query is the data preparation engine built into modern versions of Excel, found under the Data tab as Get Data or Get & Transform. It connects to files, folders, CSV exports and databases, applies a recorded series of transformation steps, and loads the result into a worksheet table or the data model. You define the steps once and refresh them, rather than repeating the work every period.
Is Power Query better than formulas?+
For preparing and reshaping data that arrives in a consistent shape, usually yes — the steps are recorded and replayed, so the work is not repeated. For a calculation that has to sit alongside the data and respond instantly to a manual edit, formulas are better. The two work well together: Power Query prepares the data, and formulas on the loaded table do the analysis.
What is an anti join in Power Query?+
An anti join returns the rows that do not match. A Left Anti join returns every row from the first table that has no match in the second, which is exactly the missing-records question in a reconciliation. A Right Anti join does the same from the other direction. Between them they give you both sides of a missing-records comparison without a single formula.
Does Power Query change my source data?+
No. Power Query reads the source and never writes back to it. That is a genuine safety advantage over a macro — a query cannot corrupt the file it is reading. The source has to be available at refresh time though, so a query built on a file that has been moved or renamed will fail until the source step is updated.
Why does my Power Query fail after I renamed a column in the source?+
Because the recorded step refers to the column by its old name. Power Query stores the column names it saw at the time each step was created, so a rename in the source breaks the step that referenced it. The fix is to open the query, find the step that errors, and update the reference — which is exactly the class of failure a macro has too, but here it is visible as a red error rather than a silent wrong result.
Is Power Query available on Excel for Mac?+
Power Query has been available in Excel for Windows for longer than on Mac, and the Mac implementation has historically had some gaps. If you are on a Mac, check what your version provides before designing a process around it — particularly the From Folder combine, which is the feature most finance workflows depend on.