Power Query – Data Transformation

Module 3: Data Transformation Techniques

Objective

In this module, you'll learn how to clean and reshape data in Power Query using built-in transformation tools.

Files Used

• Download Survey_Results.xlsx
• Download Orders_2023.xlsx

Removing Rows and Columns

Steps:

1. Import the file Survey_Results.xlsx
2. In Power Query Editor:
   • Go to the Home tab → Remove Rows → Remove Blank Rows
   • Select any unnecessary columns → Right-click → Remove Columns

Changing Data Types

Steps:

1. Click the icon next to a column header (e.g. ABC123)
2. Choose appropriate type (e.g. Whole Number, Date, Text)
3. Or: go to the Transform tab → Data Type

Renaming Columns

Steps:

1. Double-click the column header, or
2. Right-click → Rename → Type the new name

Filtering Data

Steps:

1. Click the filter icon in any column header
2. Use checkboxes, number filters, or text search to apply filters

Sorting Data

Steps:

1. Click the dropdown arrow on any column
2. Choose Sort Ascending or Sort Descending

Grouping Data

Steps:

1. Go to the Transform tab → Click Group By
2. Choose the column to group on (e.g. Product or Region)
3. Choose an aggregation (e.g. Sum, Count, Average)

Splitting Columns

Steps:

1. Select a column with combined values (e.g. full name or product code)
2. Go to the Home tab → Split Column
3. Choose method: by delimiter, number of characters, etc.

Unpivoting Data

Use case: When you have month names as column headers and want to convert them into rows.

Steps:

1. Import the file Orders_2023.xlsx
2. Select the columns with month names
3. Go to the Transform tab → Unpivot Columns

Reordering Columns

Steps:

1. Drag and drop columns directly in the preview grid, or
2. Right-click a column → Move → choose position (Left, Right, Beginning, End)

Duplicating and Referencing Queries

Steps:

1. In the Queries pane, right-click a query
2. Choose:
   • Duplicate – makes a copy of the query with independent steps
   • Reference – creates a new query that uses the output of the original

---

→ Continue to Module 4: Automation and Parameters

Return to Main Page

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises