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
Removing Rows and Columns
Steps:
- Import the file Survey_Results.xlsx
- 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:
- Click the icon next to a column header (e.g. ABC123)
- Choose appropriate type (e.g. Whole Number, Date, Text)
- Or: go to the Transform tab → Data Type
Renaming Columns
Steps:
- Double-click the column header, or
- Right-click → Rename → Type the new name
Filtering Data
Steps:
- Click the filter icon in any column header
- Use checkboxes, number filters, or text search to apply filters
Sorting Data
Steps:
- Click the dropdown arrow on any column
- Choose Sort Ascending or Sort Descending
Grouping Data
Steps:
- Go to the Transform tab → Click Group By
- Choose the column to group on (e.g. Product or Region)
- Choose an aggregation (e.g. Sum, Count, Average)
Splitting Columns
Steps:
- Select a column with combined values (e.g. full name or product code)
- Go to the Home tab → Split Column
- 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:
- Import the file Orders_2023.xlsx
- Select the columns with month names
- Go to the Transform tab → Unpivot Columns
Reordering Columns
Steps:
- Drag and drop columns directly in the preview grid, or
- Right-click a column → Move → choose position (Left, Right, Beginning, End)
Duplicating and Referencing Queries
Steps:
- In the Queries pane, right-click a query
- 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
