Power Query in Excel: The Most Underused Feature in Your Business

There’s a feature built into every copy of Excel from 2016 onwards that most business owners have never heard of. It automates the most tedious part of working with spreadsheets — the data preparation — and it does it without a single line of code.

It’s called Power Query. And if you spend any amount of time copying data between files, cleaning up exports, merging tables, or reformatting reports before you can actually analyse them, this feature will save you hours every week.

This isn’t theory. This is a practical guide to what Power Query does, when to use it, and how to get started.

What Power Query Actually Does

Power Query is a data transformation tool. It connects to a data source (a CSV file, another Excel workbook, a folder of files, a database, even a web page), pulls the data in, lets you clean and reshape it, and loads the result into your spreadsheet.

The critical difference between Power Query and manually copying data: every step you take is recorded. When the source data changes — next month’s export, an updated file from a supplier, a new bank statement — you click “Refresh” and Power Query runs every step again automatically. Same transformations, new data, zero manual work.

Think of it as a recipe. You teach Power Query how to prepare the data once. From then on, it follows the recipe every time you ask.

Where Power Query Saves Real Time

Here are the scenarios where Power Query makes an immediate, tangible difference:

Combining Monthly Reports

You get a CSV export every month — from your POS system, your bank, your accounting software, or a supplier portal. You need to combine twelve months into one table for analysis. Manually, that’s opening twelve files, copying and pasting, checking for format differences, and hoping nothing gets misaligned.

With Power Query, you point it at a folder. It reads every file in that folder, combines them into one table, and applies your cleaning steps. Next month, you drop the new file in the folder, click Refresh, and it’s included automatically.

Cleaning Up Messy Exports

System exports are rarely analysis-ready. Column headers are wrong. Dates are in American format. There are blank rows, merged cells, or summary rows mixed in with the data. Numbers are stored as text. Currency symbols are embedded in the values.

Power Query handles all of this with point-and-click transformations: rename columns, change data types, remove rows, split columns, replace values. Each step appears in a list on the right side of the editor, and you can modify or reorder them at any time.

Merging Data From Multiple Sources

You have sales data in one file and customer details in another. You need to combine them — like a VLOOKUP, but across entire tables. Power Query’s “Merge Queries” function joins two tables on a common column, exactly like a database join. It handles duplicates, mismatches, and multiple join columns cleanly. It can also pivot and unpivot data — reshaping tables from wide to tall or tall to wide — in a single click.

A Practical Walkthrough

Here’s a real-world example. Every month, your bookkeeper exports a transaction list from the bank as a CSV. You need to categorise each transaction, remove internal transfers, and produce a summary by category.

Without Power Query: Open the CSV. Delete the header rows the bank adds. Fix the date column. Remove transfers. Manually categorise each new transaction. Build a summary with SUMIFS. Repeat next month.

With Power Query:

1. Connect — point Power Query at the CSV file
2. Clean — remove the bank’s header rows, set the date column to a proper date type, filter out rows containing “Transfer” or “Internal”
3. Categorise — merge with a lookup table that maps transaction descriptions to categories (you maintain this once and it grows over time)
4. Load — output the cleaned, categorised data to a sheet, then summarise with a pivot table

Next month, click Refresh and the entire process runs again. You only need to categorise genuinely new descriptions.

What Power Query Can’t Do

Power Query is excellent at its job, but its job is specifically data preparation — getting data into the right shape for analysis. It doesn’t do everything:

• It’s not real-time. Power Query runs when you click Refresh (or on a schedule in Power BI). It doesn’t update live as source data changes.
• It doesn’t write back. Power Query reads and transforms data. It can’t update the source systems. It’s a one-way street.
• It doesn’t replace a database. The output is still an Excel spreadsheet with all the usual limitations — row limits, file size, no multi-user editing.
• It doesn’t trigger actions. Power Query can tell you that a supplier’s prices increased, but it can’t send an alert or update your quoting system.
• Complex transformations hit a ceiling. Some data manipulation tasks that are straightforward in Python or SQL become convoluted in Power Query’s visual editor.

When Power Query Isn’t Enough

Power Query is a significant upgrade from manual data prep. But it’s still operating within Excel’s world. For many businesses, it buys genuine time — months or even years of improved productivity before hitting the next ceiling.

You’ve outgrown Power Query when:

• You need data from systems Power Query can’t connect to — many SaaS platforms require API access, and Power Query’s web connectors have limitations
• Data volumes exceed Excel’s limits — Power Query can process large datasets, but the output still lands in a spreadsheet with a million-row cap
• Multiple people need the same transformed data — Power Query lives inside a single workbook, so sharing means sharing the file
• Transformed data needs to feed other systems — if the output needs to flow into a CRM, invoicing system, or dashboard, you’re back to manual exports
• The transformation logic becomes business-critical — when reporting depends on a Power Query recipe inside one Excel file on someone’s laptop, you’ve recreated the macro problem with a different tool

Getting Started With Power Query Today

If you’ve never used Power Query, start small:

• Pick one report you rebuild every month — the one where you spend the most time on data prep
• Set up Power Query to automate the prep — connect to the source file, record the cleaning steps, load the result
• Next month, test the refresh — update the source file and click Refresh. If it works, you’ve just automated your first data pipeline
• Learn “Append” and “Merge” — these two operations handle 80% of what business owners need. Append stacks tables on top of each other (combining months). Merge joins tables side by side (like VLOOKUP). Master these and you’re ahead of most Excel users

Power Query solves the problem that wastes the most time — not analysis, but the tedious prep work before analysis can begin. Learn it, use it, and when you eventually outgrow it, you’ll have a clear understanding of what your data pipeline needs to do — which makes the conversation about proper automation much easier.