Module 3
5 min read
Data Transformation
Advanced techniques for transforming and shaping data
Text Transformations
| Task | How |
|---|---|
| Uppercase/Lowercase | Transform > Format > UPPERCASE |
| Remove spaces | Transform > Format > Trim |
| Extract text | Transform > Extract > First Characters |
| Replace values | Home > Replace Values |
Number Transformations
| Task | How |
|---|---|
| Round numbers | Transform > Rounding > Round |
| Multiply/Divide | Transform > Standard > Multiply |
| Add/Subtract | Transform > Standard > Add |
Date Transformations
| Task | How |
|---|---|
| Get Year | Transform > Date > Year |
| Get Month | Transform > Date > Month |
| Get Day | Transform > Date > Day |
Conditional Column
Add a column based on IF logic.
Add Column > Conditional Column
Example: Categorize sales as High/Low
- IF Sales >= 1000 THEN "High"
- ELSE "Low"
Custom Column
Write your own formula.
Add Column > Custom Column
Examples:
- Full Name:
[FirstName] & " " & [LastName] - Profit:
[Revenue] - [Cost] - Year:
Date.Year([OrderDate])
Unpivot Columns
Convert wide data to long format.
Before (Wide):
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Apple | 100 | 150 | 200 |
After (Long):
| Product | Month | Sales |
|---|---|---|
| Apple | Jan | 100 |
| Apple | Feb | 150 |
How: Select columns > Transform > Unpivot Columns
Merge Queries (Join Tables)
Combine two tables by a matching column.
Home > Merge Queries
- Select first table and key column
- Select second table and key column
- Choose join type (Left, Inner, etc.)
- Expand the new column
Append Queries (Stack Tables)
Stack tables vertically.
Home > Append Queries
Use when you have Jan, Feb, Mar as separate tables and want one combined table.
Power Query remembers all your steps. When source data updates, transformations run automatically.