If you regularly receive data from external sources — CSV exports from a CRM, downloaded bank statements, weekly sales reports from colleagues, or data dumps from web applications — you probably spend a significant amount of time manually cleaning and reformatting that data before you can use it in Excel. You remove blank rows, fix inconsistent date formats, split full names into first and last name columns, trim extra spaces, and correct capitalisation. Every week, you repeat the same tedious process.
Power Query is a built-in Excel tool (available in Excel 2016 and later, and Microsoft 365) that automates this entire workflow. You define your data import and cleaning steps once, and Power Query remembers them. The next time you receive updated data, you simply click Refresh, and Power Query re-applies every transformation automatically.
Where to Find Power Query in Excel
Power Query is accessed from the Data tab on the Excel ribbon. Look for the Get & Transform Data group on the left side of the ribbon. The key buttons are:
- Get Data — Opens a menu showing all available data sources (files, databases, web pages, and more).
- From Text/CSV — A shortcut to import CSV or text files directly.
- From Web — Import data from a web page.
- From Table/Range — Load data that is already in your current workbook into Power Query for transformation.
Importing Data from a CSV File
The most common use case for Power Query is importing and cleaning CSV files. Here is a step-by-step walkthrough:
- Click the Data tab on the ribbon.
- Click From Text/CSV.
- Navigate to your CSV file and click Import.
- Excel displays a preview of the data. Check that the delimiter (comma, semicolon, tab) is detected correctly and that the column headers look right.
- Instead of clicking Load (which would import the raw data as-is), click Transform Data. This opens the Power Query Editor.
The Power Query Editor is where the real power lies. It shows your data in a spreadsheet-like view with a panel on the right called Applied Steps, which records every transformation you make.
Common Data Cleaning Transformations
Removing Blank Rows
Go to Home > Remove Rows > Remove Blank Rows. Power Query removes any row where every single column is empty. This is faster and more reliable than manually hunting for blank rows in a large dataset.
Changing Data Types
Power Query automatically detects column data types, but it does not always get them right. Click on a column header, then use the Data Type dropdown on the ribbon (or right-click the column header and select Change Type) to set the correct type: Text, Whole Number, Decimal Number, Date, Date/Time, or Boolean.
Getting data types right is critical. If a column of dates is treated as text, Excel cannot sort or filter it chronologically.
Splitting Columns
If a column contains combined data (for example, “John Smith” in a single “Full Name” column), you can split it:
- Select the column.
- Go to Transform > Split Column > By Delimiter.
- Choose the delimiter (space, comma, semicolon, etc.).
- Power Query creates two new columns automatically.
Trimming and Cleaning Text
Select a text column, then go to Transform > Format. You will find options to:
- Trim — Remove leading and trailing spaces.
- Clean — Remove non-printable characters (such as line breaks or tab characters hidden inside cells).
- UPPERCASE, lowercase, or Capitalize Each Word — Standardise capitalisation across the entire column.
Filtering Rows
Click the dropdown arrow on any column header to filter rows, exactly like Excel’s standard AutoFilter. You can filter by value, by condition (“greater than”, “contains”, “does not equal”), or by removing errors. Filtered-out rows are permanently excluded from the output.
Renaming Columns
Double-click any column header to rename it. This is essential when importing data with cryptic column names like “Col1”, “field_23”, or “data_export_v2_final”.
Reordering and Removing Columns
Drag column headers to reorder them, or right-click a column and select Remove to delete columns you do not need. You can also right-click a column and select Remove Other Columns to keep only the columns you want.
The Applied Steps Panel: Your Transformation Recipe
Every action you take in the Power Query Editor is recorded in the Applied Steps panel on the right side. Each step has a descriptive name like “Removed Blank Rows”, “Changed Type”, “Split Column by Delimiter”.
You can:
- Click on any step to see the data at that point in the transformation process.
- Delete a step by clicking the X next to it.
- Reorder steps by dragging them up or down (though some reorderings may cause errors if steps depend on each other).
- Rename steps to make the transformation recipe easier to understand.
This step-by-step recording is what makes Power Query so powerful. The entire cleaning process becomes a reusable, auditable recipe.
Loading the Cleaned Data into Excel
When you are satisfied with the transformations, click Close & Load on the Home tab. Power Query inserts the cleaned data into a new worksheet as an Excel Table.
If you want more control over where the data goes, click the dropdown arrow under Close & Load and select Close & Load To. You can choose to load the data into an existing worksheet, create a PivotTable directly from the query, or load it as a connection only (useful for very large datasets that you do not need to see in a worksheet).
Refreshing Data When the Source Updates
The most valuable feature of Power Query is the ability to refresh. When your data source is updated (for example, you receive a new CSV file with this week’s sales data), simply:
- Replace the old CSV file with the new one (keeping the same file name and location).
- Go to the Data tab and click Refresh All.
Power Query re-imports the file and re-applies every transformation step automatically. In seconds, you have a perfectly cleaned, formatted dataset — without touching a single cell manually.
Practical Use Cases
- Monthly financial reports: Import bank statement CSVs, categorise transactions, remove duplicates, and generate a clean expense summary.
- CRM data cleanup: Import customer records, split combined address fields, standardise phone number formats, and remove incomplete entries.
- HR data processing: Import employee data exports, fix date formats, calculate tenure from hire dates, and prepare payroll summaries.
- Web data imports: Use the “From Web” connector to pull tables directly from web pages into Excel, then clean and reshape them using Power Query.
Power Query is one of Excel’s most powerful features, yet most users have never opened it. If you spend more than fifteen minutes each week manually cleaning imported data, learning Power Query will save you hours every month.