How to Use Power Query in Microsoft Excel to Automate Data Cleaning

Introduction

Data analysts often spend a significant amount of time manually cleaning and formatting raw data exports before they can be analyzed. Microsoft Excel includes a powerful built-in tool called Power Query, which allows you to record a series of data transformation steps. Once configured, Power Query can automatically clean new data with a single click. This guide explains how to import and automate data cleaning using Power Query.

Step 1: Import Data into Power Query

Instead of copying and pasting your raw data into a spreadsheet, you will connect Excel directly to the source file.

1. Open a blank Excel workbook.
2. Navigate to the Data tab on the ribbon.
3. Click Get Data > From File > From Text/CSV (or choose From Workbook if your raw data is in another Excel file).
4. Locate and select your raw data file, then click Import.
5. A preview window will appear. Do not click Load. Instead, click Transform Data. This opens the Power Query Editor.

Step 2: Perform Data Transformations

The Power Query Editor provides a visual interface for cleaning data. Every action you take is recorded in the “Applied Steps” pane on the right. Common cleaning tasks include:

• Removing Columns: Right-click a column header and select Remove to delete unnecessary data.
• Splitting Columns: If a column contains combined data (e.g., “LastName, FirstName”), select it, click Split Column > By Delimiter on the Home tab, and choose the comma.
• Changing Data Types: Click the small icon on the left side of a column header (e.g., “ABC” or “123”) to ensure dates are recognized as Dates and numbers as Whole Numbers.
• Replacing Values: Select a column, go to the Transform tab, and click Replace Values to fix common typos or nulls.
• Promoting Headers: If your first row of data contains the column names, click Use First Row as Headers on the Home tab.

Step 3: Load the Clean Data

Once your data looks correct in the preview, it is time to load it into your workbook.

1. On the Home tab of the Power Query Editor, click Close & Load.
2. Excel will create a new worksheet and insert a green formatted table containing your perfectly cleaned data.

Step 4: Automate the Refresh Process

The true power of this tool is automation. When you receive an updated raw data file next week or next month, you do not need to repeat the cleaning steps.

1. Save the new raw data file with the exact same name and in the exact same folder location, overwriting the old file.
2. Open your Excel workbook containing the Power Query table.
3. Right-click anywhere inside the green table and select Refresh.

Power Query will reach out to the file, re-apply all your recorded transformation steps in seconds, and update the table with the new, clean data. This eliminates the need for complex VBA macros and manual formatting.