Power Query – Data Transformation

Module 3: Data Transformation Techniques

Objective

Clean and reshape data using Power Query’s built-in tools.

Files Used

• Download Survey_Results.xlsx
• Download Orders_2023.xlsx

Removing Rows and Columns

Steps:

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

Changing Data Types

Steps:

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

Renaming Columns

Steps:

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

Filtering and Sorting

Steps:

1. Click filter icon in column header
2. Use built-in filters or checkboxes
3. Click arrow again to access Sort Ascending / Sort Descending

Grouping Data

Steps:

1. Select a column
2. Go to Transform tab → Group By
3. Choose operation: Sum, Count, etc.

Splitting Columns

Steps:

1. Select a column (e.g. Full Name)
2. Go to Home tab → Split Column
3. Choose method (e.g. by delimiter)

Unpivoting Columns

Steps:

1. Load Orders_2023.xlsx
2. Select month columns (e.g. Jan, Feb)
3. Go to Transform tab → Unpivot Columns

Reordering Columns

Steps:

1. Drag columns to new position or
2. Right-click column → Move → choose direction

Duplicating and Referencing Queries

Steps:

1. Right-click a query in the left pane
2. Choose:
   • Duplicate – creates a full copy
   • Reference – creates a linked copy

Additional Exercise: Cleanup and Pivot Prep

Goal: Prepare survey data for analysis.

Steps:

1. Remove rows with missing satisfaction scores
2. Change column names to lowercase
3. Group by satisfaction rating and count rows
4. Load results into a new sheet

Extension: Conditional Columns

Goal: Create a new column that classifies satisfaction as Positive/Neutral/Negative.

Steps:

1. Go to the Add Column tab → Conditional Column
2. Name: `SatisfactionLevel`
3. Logic:
   • If Satisfaction = 4 or 5 → "Positive"
   • If Satisfaction = 3 → "Neutral"
   • If Satisfaction = 1 or 2 → "Negative"
4. Click OK

---

→ 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