Transforming and Cleaning Data with Power Query in Excel

Power Query is a powerful data preparation tool in Microsoft Excel that allows users to connect to multiple data sources, clean and transform data, and automate repetitive data preparation tasks. Instead of manually editing datasets, Power Query records each transformation as a reusable step, making data processing faster, more accurate, and easier to maintain.

Businesses use Power Query to import sales reports, merge customer records, clean inconsistent data, remove duplicates, combine files, and prepare datasets for Pivot Tables, dashboards, and advanced analysis.

Learning Objectives

• Understand Power Query

• Import data from different sources

• Clean and transform data

• Change data types

• Remove duplicates

• Merge and append queries

• Load transformed data back into Excel

What is Power Query?

Power Query is an Extract, Transform, and Load (ETL) tool built into Microsoft Excel.

It enables you to:

• Import data

• Clean inconsistent information

• Combine multiple datasets

• Transform data automatically

• Refresh reports without repeating manual steps

Opening Power Query

Go to:

Data → Get Data

Common data sources include:

• Excel Workbook

• CSV Files

• Text Files

• SQL Server

• Microsoft Access

• Web

• SharePoint

Importing Data

Suppose you have a CSV file named:

SalesData.csv

Steps:

1. Go to Data → Get Data

2. Choose From File → From Text/CSV

3. Select the file

4. Click Load or Transform Data

Choosing Transform Data opens the Power Query Editor.

Understanding the Power Query Editor

The Power Query Editor contains:

• Queries Pane

• Data Preview

• Applied Steps

• Ribbon

• Formula Bar (optional)

Every transformation performed is automatically recorded in Applied Steps, allowing it to be repeated whenever the data is refreshed.

Cleaning Data

Power Query makes data cleaning straightforward.

Common tasks include:

• Remove blank rows

• Remove duplicate records

• Trim spaces

• Replace values

• Split columns

• Merge columns

• Fill missing values

• Rename columns

Example:

Select the Customer Name column.

Choose:

Transform → Format → Trim

This removes extra spaces from text values.

Changing Data Types

Each column should have the correct data type.

Examples:

• Text

• Whole Number

• Decimal Number

• Date

• Currency

To change a data type:

1. Select the column.

2. Click the data type icon.

3. Choose the correct type.

Using correct data types improves calculations and Pivot Tables.

Merging Queries

Merge combines two datasets using a common field.

Example:

Sales Table

Products Table

Steps:

1. Home → Merge Queries

2. Select both tables

3. Choose Product ID

4. Select Left Outer Join

5. Expand the merged columns

The result combines sales with product information.

Appending Queries

Appending stacks one dataset below another.

Example:

January Sales

February Sales

After appending:

Combined Sales Report

Steps:

1. Home

2. Append Queries

3. Select both tables

4. Click OK

Appending is useful when combining monthly or yearly reports.

Loading Data into Excel

After completing the transformations:

Choose:

Home → Close & Load

Power Query creates a refreshed Excel table.

Whenever the original data changes:

Data → Refresh All

updates the imported table automatically.

Benefits of Power Query

• Eliminates repetitive manual work

• Automates data cleaning

• Connects multiple data sources

• Supports large datasets

• Improves reporting accuracy

• Simplifies dashboard preparation

Best Practices

• Keep original data unchanged.

• Rename queries clearly.

• Remove unnecessary columns.

• Apply correct data types.

• Refresh queries regularly.

• Review Applied Steps before loading data.

Common Mistakes

• Loading data before cleaning.

• Using incorrect data types.

• Forgetting to refresh queries.

• Importing duplicate records.

• Deleting important transformation steps.