Skip to content
All articles

Excel & VBA

Excel Power Query Tutorial: Clean Any Dataset in Minutes

Updated · First published · 8 min read

A gloved hand holding a yellow spray bottle

Power Query transforms hours of manual data cleanup into a one-click refresh. Learn the step-by-step process for building reusable data pipelines in Excel.

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

Change a column's type by clicking the type icon (ABC or 123) on the left of its header: 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 uses the same engine and M language as 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 includes a full Power Query module, and our Power BI training takes it further with advanced M language and dataflows.

Learn Microsoft Excel live

Master formulas, PivotTables, and dashboards that turn raw data into business insights. Three levels, two days each.

Keep reading

Let us build something great

Get in touch