Messy data is one of the most common frustrations in any office. Inconsistent date formats, duplicate rows, blank cells, and mismatched text cases can turn a simple reporting task into hours of manual correction. Excel’s Power Query tool was designed to eliminate exactly this kind of drudgery — and once you understand how it works, you will wonder how you ever managed without it.
Power Query is available in Microsoft Office 2024 Professional Plus and all recent versions of Excel for Windows. It lets you connect to a data source, apply a series of transformation steps, and load the cleaned result into your spreadsheet — and crucially, you can refresh that entire process with a single click whenever your source data updates.
What Is Power Query and Why Does It Matter?
Power Query is an Extract, Transform, Load (ETL) engine built directly into Excel. It works by recording each transformation you apply as a step in a query. Those steps are stored in a language called M (or Power Query Formula Language), though you rarely need to write M code manually — the interface handles it for you.
The key advantage over manual data cleaning is repeatability. If you receive a new export from your CRM, accounting system, or website analytics platform every week, Power Query will apply every transformation you defined previously the moment you hit Refresh. No copy-pasting, no manual find-and-replace, no formula arrays to maintain.
Connecting to a Data Source
To open Power Query, go to the Data tab in Excel and click Get Data. From here you can connect to a remarkable range of sources: Excel files, CSV files, SQL databases, SharePoint lists, web pages, OData feeds, and many more.
For most office users, the most common starting point is a CSV or Excel file exported from another system. Select Get Data > From File > From Text/CSV (or From Workbook), navigate to your file, and click Transform Data rather than Load. This opens the Power Query Editor, where all the real work happens.
Essential Cleaning Operations
Removing Duplicates
Select the column or columns that should uniquely identify a row, then go to Home > Remove Rows > Remove Duplicates. Power Query will keep only the first occurrence of each unique combination. This is far more reliable than using Excel’s built-in Remove Duplicates feature because it becomes part of your reusable query.
Trimming and Cleaning Text
Exported data often contains leading and trailing spaces that cause VLOOKUP mismatches and pivot table errors. Right-click any text column, choose Transform, and select Trim. While you are there, Clean will remove non-printable characters, and you can standardise capitalisation using Uppercase, Lowercase, or Capitalise Each Word.
Fixing Date Formats
One of the most persistent data quality problems in British offices is date format confusion — particularly when data comes from American systems that use MM/DD/YYYY. In Power Query, right-click your date column, select Change Type > Using Locale, and choose the appropriate locale (e.g., English (United Kingdom)) along with the Date type. Power Query will interpret the dates correctly regardless of your system’s regional settings.
Splitting and Merging Columns
If a full name has arrived in a single column but you need separate first and last name fields, select the column and use Transform > Split Column > By Delimiter. Choose Space as the delimiter, and Power Query will split accordingly. The reverse — merging columns — is equally straightforward via Add Column > Merge Columns.
Replacing and Filtering Values
To normalise inconsistent entries (such as UK, United Kingdom, and Britain all meaning the same country), right-click the column, select Replace Values, and enter the original and replacement text. For filtering, simply click the dropdown arrow on any column header — just like filtering a standard Excel table, except this filter becomes a permanent step in your query.
Transforming Data with Custom Columns
Power Query’s Add Column > Custom Column feature lets you create calculated fields using M formulas. For example, to calculate a margin percentage from Cost and Revenue columns you might write:
([Revenue] - [Cost]) / [Revenue]This is conceptually similar to adding a formula column in Excel, but the column is generated fresh every time the query refreshes, so there is nothing to copy down or maintain manually.
Combining Multiple Files Automatically
One of Power Query’s most impressive features is its ability to combine all files in a folder into a single table. This is invaluable if you receive monthly export files that follow the same structure. Go to Get Data > From File > From Folder, point it at your folder, and Power Query will stack all the files on top of one another. Add a new file to the folder, click Refresh, and it appears automatically in your combined dataset.
Loading Your Cleaned Data
Once your query steps are complete, click Close & Load in the Home tab. You can load the data into a new worksheet as a table, add it to the Data Model for use with Power Pivot, or create a connection-only query to use as a source for further queries. The loaded table will have a refresh button — or you can set it to refresh automatically when the workbook opens via Query Properties > Refresh every X minutes.
Scheduling and Automating Refreshes
For workbooks stored on SharePoint or OneDrive, you can trigger refreshes via Power Automate, ensuring your reports always show current data without anyone needing to open the file manually. For local files, a simple macro can call ThisWorkbook.RefreshAll on a schedule using Windows Task Scheduler.
Getting Started with the Right Version of Excel
Power Query’s capabilities expand with each Office release. The version bundled with Office 2024 Professional Plus for Windows (available from GetRenewedTech for €34.99) includes the latest connector library and query performance improvements. If you are on a Mac, Office 2024 for macOS (€58.99) also includes Power Query, though the Mac version has fewer data connectors than its Windows counterpart.
Power Query fundamentally changes how you work with data. Instead of treating data cleaning as a one-off chore, you build a reusable pipeline that does the work for you, every time. Invest a couple of hours learning it properly and you will recoup that time many times over within weeks.
