Power Query is built into every modern version of Excel, yet most users have never touched it. It is the single most underused feature in Excel, and once you try it, you will wonder how you worked without it. This tutorial shows you how to build a reusable data cleaning pipeline step by step.
The problem Power Query solves
Every week, you download a report. It has messy headers, inconsistent dates, extra columns, duplicate rows, and values in the wrong format. You spend 30 minutes cleaning it manually. Next week, the same report arrives and you do it all over again. Power Query records your cleaning steps once and replays them with a single click on every future refresh.
Accessing Power Query
In Excel, go to the Data tab and click Get Data. You can also click From Table/Range to transform data already in your workbook. Both paths open the Power Query Editor, which is a separate window where all transformations happen. Your original data is never modified. Power Query creates a transformed copy that loads into a new table when you are done.
Step 1: Connect to your data source
Click Get Data and choose your source: Excel workbook, CSV, folder of files, database, web page, or dozens of others. For this tutorial, use a CSV. Select the file, and Power Query shows a preview. Click Transform Data (not Load) to open the editor where you can clean the data before loading it.
Step 2: Remove unnecessary columns
Right-click any column header and choose Remove. Or select the columns you want to keep, right-click, and choose Remove Other Columns. Each action appears as a named step in the Applied Steps pane on the right. You can click any step to see the data at that point, or delete a step to undo it.
Step 3: Filter and clean rows
Click the dropdown arrow on a column header to filter by value. Remove blank rows by going to Home, Remove Rows, Remove Blank Rows. Remove duplicate rows with Home, Remove Rows, Remove Duplicates. Each of these becomes a step in your pipeline.
Step 4: Change data types
Click a column header and change its type using the dropdown on the left of the formula bar: Text, Number, Date, Currency, etc. Getting types right early prevents calculation errors later. Power Query is much better at detecting and converting data types than Excel's built-in formatting.
Step 5: Transform text and dates
Select a text column and use the Transform tab to trim whitespace, change case (upper, lower, proper), or extract parts of text (first characters, last characters, between delimiters). For dates, you can extract year, month, day, or day of week. These operations replace complex formulas you would otherwise write in the worksheet.
Step 6: Merge queries (Power Query joins)
If you have two data sources that need to be combined (like sales data and product lookup data), use Home, Merge Queries. Choose the key columns from each table and select the join type: Left Outer (keep all rows from the first table), Inner (keep only matches), etc. This replaces VLOOKUP for recurring data connections.
Step 7: Load the cleaned data
When your data is clean, click Close & Load. Power Query creates a new Excel table with the transformed data. Every column is properly typed, every row is clean, and the pipeline is saved. Next time your source data updates, just right-click the table and click Refresh. All your cleaning steps run automatically in seconds.
Best practices
Rename your steps descriptively (double-click the step name in Applied Steps). Remove rows and columns as early as possible to keep the pipeline fast. Use Append Queries to stack multiple files with the same structure (perfect for monthly reports). Save your workbook regularly since the Power Query definition is stored with the file.
Next steps
Power Query in Excel is identical to Power Query in Power BI. Once you learn it in Excel, you can use the same skills in Power BI for building data models and automated dashboards. Our [Excel training](/courses/microsoft-excel/) includes a full Power Query module, and our [Power BI training](/courses/power-bi/) takes it further with advanced M language and dataflows.