Power Query: how to stop rebuilding the same spreadsheet every month

21 August 2026
By Matt Weston

Power Query is the part of Excel that most people have never opened, and it is the part that would save them the most time. It sits on the Data tab under Get and Transform, and its job is to take messy data from somewhere else, clean it up, and drop it into your spreadsheet in a usable state.

The reason it matters is repetition. Almost every business has someone who spends the first morning of the month doing the same thing: exporting a report, deleting the top four rows, splitting a column, fixing the date format, removing blanks, and pasting the result into a template.

That work is identical every time, which is exactly the kind of work a computer should be doing.

What follows is what Power Query actually is, how the editor works, a worked example you can follow with your own data, and an honest look at where it stops being the right tool. If a monthly report currently eats half a day, this is the feature that gives that half day back.

Power Query

Quick summary

It is built into Excel 2016 and later on Windows, and into Microsoft 365 on both Windows and Mac, so most people already have it without knowing
Every action you take is saved as a step, and those steps re-run in order whenever you refresh, which is what turns a manual routine into a one-click job
It connects to far more than spreadsheets, including CSV files, folders, databases, SharePoint lists and web pages
It never alters your source data, so there is no risk of damaging the original file while experimenting
As of early 2026, the full experience is available in Excel for the web, having previously been desktop-only

The saving is not in doing the job faster once. It is in never doing it manually again, which is a different order of benefit entirely.

What is Power Query?

Power Query is a data connection and preparation tool built into Excel, used to import data from another source, transform it into the shape you need, and load the result into your workbook. Microsoft also refers to it as Get and Transform, which is the name of the group it sits in on the Data tab, and the same engine powers Power BI.

The concept that makes it click is that Power Query records what you do. When you delete a column, split a field or filter out blank rows, it does not simply perform that action, it writes it down as a step. Those steps are kept in order as a recipe. Next month, when the source file has been replaced with fresh data, you press Refresh and every step re-runs against the new figures automatically. You build the process once and reuse it indefinitely. Microsoft’s own overview, about Power Query in Excel, sets out the full picture.

It is also worth saying plainly that Power Query requires no code. The interface is entirely point and click, and the underlying M language exists for advanced cases rather than everyday use. People who would never write a macro use Power Query comfortably

Where to find it, and which versions have it

Power Query lives on the Data tab, in the Get and Transform Data group. If you can see a Get Data button there, you have it.

Availability is more generous than people assume. Power Query is built into all Excel 2016 and later standalone versions on Windows, and into every Microsoft 365 subscription plan. Microsoft 365 always holds the complete feature set, while perpetual versions such as Excel 2021 hold a large subset rather than all of it. On Mac it is available to Microsoft 365 subscribers via the Get Data command. Microsoft maintains a version-by-version breakdown of Power Query data sources in Excel versions if you need the detail.

One recent change is worth knowing about, because it removes a long-standing reason people stayed tethered to the desktop app. Power Query is now fully available in Excel for the web, so importing and transforming data in a browser is possible where previously it was not.

How the Power Query Editor works

The editor opens in its own window, separate from your spreadsheet, and it has three parts worth knowing before you start clicking.

The data preview

The centre of the window shows a preview of your data as it currently stands. This is a preview only, not your real workbook, so nothing you do here touches the original file until you choose to load the result.

The Applied Steps pane

On the right sits the list of every action you have taken, in order. This is the most important part of the whole tool. You can click any step to see the data as it looked at that point, reorder steps, edit a step’s settings, or delete one you got wrong. It is the closest thing Excel has to a proper undo history that survives being saved and closed.

The ribbon

The Home, Transform and Add Column tabs hold the operations: removing columns, splitting them, changing data types, filtering rows, grouping, merging queries together and so on. Every button you press adds a step to the pane on the right.

Understanding that relationship, that every click becomes a repeatable step, is what separates people who use Power Query occasionally from people who stop cleaning data by hand altogether.

A worked example: cleaning a monthly export

This follows the shape of the job most people actually have. Assume a CSV export from a finance or CRM system, with junk rows at the top, a combined name field and inconsistent dates.

  1. On the Data tab, select Get Data, then choose your source, for example From Text/CSV. Pick the file.
  2. In the preview window that appears, select Transform Data rather than Load. This opens the Power Query Editor instead of dumping the raw data straight into your sheet.
  3. Remove the junk at the top with Home, then Remove Rows, then Remove Top Rows, entering the number of rows to discard.
  4. Promote your real headers with Home, then Use First Row as Headers.
  5. Split a combined field by selecting the column, then Transform, then Split Column, then By Delimiter, choosing the comma or space that separates the values.
  6. Fix the data types by selecting each column and setting the type explicitly, for example Date for a date column and Whole Number for a quantity. This prevents the sorting and calculation problems that plague imported data.
  7. Filter out the rows you do not want using the dropdown arrow on any column header, for example removing blanks or excluding a cancelled status.
  8. Select Home, then Close and Load to send the finished table into your workbook.

The following month, replace the source file with the new export, right-click your table and choose Refresh. Every step above re-runs in order, and the clean table rebuilds itself.

Power Query, formulas, or Power BI?

The overlap between these confuses people, and choosing badly means either doing work by hand that could be automated, or over-engineering something that a formula would have handled. The distinction is cleaner than it first appears:

Best forRuns whenTypical use
FormulasCalculating within data you already haveContinuously, as the sheet recalculatesLookups, sums, conditional logic
Power QueryGetting data in and reshaping itOn refresh, when you ask it toCleaning exports, combining files, monthly reports
Power BIAnalysing and visualising at scaleOn a scheduled refreshDashboards, large datasets, sharing insight widely

The practical rule is that Power Query handles everything that happens before your data is usable, and formulas handle what happens after. If you are cleaning, you want Power Query. If you are calculating, you want a formula such as XLOOKUP. If you have outgrown the spreadsheet entirely, that is the point at which Power BI starts to earn its place, and usefully, the Power Query skills carry straight across, because Power BI uses the same engine.

Common mistakes worth avoiding

A handful of habits account for most of the frustration people report when they start using Power Query.

  • Loading before transforming. Selecting Load rather than Transform Data in the import window drops the raw mess into your sheet and skips the entire point. Always go to the editor first.
  • Cleaning data by hand after loading. Any manual tidy-up you do in the sheet is lost the moment you refresh. If it needs doing, it needs doing as a step in the query.
  • Ignoring data types. Imported columns often arrive as text, which quietly breaks sorting, filtering and any calculation downstream. Set the type on every column deliberately.
  • Moving or renaming the source file. As above, this is the most common breakage of all, and it is entirely preventable.
  • Building one enormous query. Where a job involves several sources, keep separate queries and merge them, rather than assembling a single query with forty steps that nobody can debug later.

Avoid those and Power Query becomes genuinely low-maintenance, which is the whole reason for using it.

Where Power Query stops being the answer

Being honest about the limits matters, because Power Query is often recommended as though it solves everything.

It is designed for scheduled, repeatable preparation, not live data. Refreshes happen when you or a schedule trigger them, so if you need figures updating second by second, this is not the tool. Excel’s grid also tops out at just over a million rows, and although loading to the Data Model raises that ceiling, genuinely large datasets belong in Power BI or a database rather than a workbook. And if several people need to update the same information continuously, the problem is not data preparation at all, it is that a spreadsheet is being used as a shared record system, which our post on Microsoft Lists covers.

Within its intended job, though, cleaning and combining data that arrives regularly in a predictable shape, very little else comes close.

Still spending the first morning of every month cleaning the same report?

The bottom line on Power Query

Power Query is the rare Excel feature that pays for the time spent learning it almost immediately. Most people who use it seriously describe the same moment: they automate one monthly routine, realise the following month takes ninety seconds instead of three hours, and then go looking for everything else that can be handled the same way.

It is already sitting on the Data tab of the Excel you have. It needs no code, it never touches your source files, and the skills transfer directly to Power BI if you outgrow the spreadsheet later. The only real prerequisite is having a repetitive data job worth automating, and most businesses have several.

If your team would rather learn this properly than piece it together, our Excel training courses run at foundation, intermediate and advanced level, and our Power BI courses pick up where Excel leaves off. Making new skills stick after training matters just as much, something Vantage 365 Support has written about separately.

Frequently asked questions

What is Power Query used for?

Power Query is used to import data from another source, clean and reshape it, and load the result into Excel. Typical jobs include stripping junk rows from a system export, splitting or combining columns, correcting data types, filtering out unwanted records, and merging several files into one table. Because every action is saved as a repeatable step, it is most valuable for work that recurs on a regular cycle.

Is Power Query free?

Yes, for anyone with a supported version of Excel. It is built into Excel 2016 and later on Windows and into every Microsoft 365 plan, at no additional cost. There is nothing to buy and, on modern versions, nothing to install.

Where is Power Query in Excel?

On the Data tab, in the Get and Transform Data group. Select Get Data to choose a source, or Launch Power Query Editor to open the editor directly. If that group is missing, you are most likely on a version older than Excel 2016.

Do I need to know how to code to use Power Query?

No. The entire interface is point and click, and every transformation has a button. Power Query does have its own language, called M, which sits behind the scenes and can be edited for advanced scenarios, but the vast majority of users never touch it.

What is the difference between Power Query and Power Pivot?

Power Query prepares data: importing it, cleaning it and shaping it before it reaches your workbook. Power Pivot models data once it is there, building relationships between tables and creating calculations across them. They are sequential rather than competing, so a typical workflow uses Power Query first and Power Pivot afterwards.

Does Power Query change my original file?

No. Power Query reads from the source and applies its steps to a copy, leaving the original file untouched. This makes it safe to experiment, and it is one reason it is a better habit than editing raw exports by hand.

Shopping Basket