Data Transformation

What You'll Learn

• Advanced text transformations
• Working with dates and numbers
• Conditional columns
• Custom columns with formulas
• Unpivot and pivot operations

Text Transformations

Uppercase/Lowercase

Standardize text:

1. Select text column
2. Transform > Format
3. Choose:
   • UPPERCASE
   • lowercase
   • Capitalize Each Word

Trim and Clean

Remove extra spaces:

1. Select column
2. Transform > Format > Trim

Removes leading/trailing spaces

Clean: Removes non-printable characters

Extract Text

Get first 3 characters:

1. Select column
2. Transform > Extract > First Characters
3. Enter count: 3

Other extracts:

• Last Characters
• Range (characters 2-5)
• Text Before Delimiter
• Text After Delimiter

Example: Extract "ABC" from "ABC-123"

• Extract > Text Before Delimiter: "-"

Replace Values

Replace "N/A" with blank:

1. Select column
2. Home > Replace Values
3. Value to Find: N/A
4. Replace With: (leave empty)

Find/Replace: Case sensitive option available!

Number Transformations

Rounding

Round to 2 decimals:

1. Select number column
2. Transform > Round
3. Choose:
   • Round Up
   • Round Down
   • Round (standard)

Decimal Places: Transform > Round > 2

Standard Math

Multiply column by 1.1:

1. Select column
2. Transform > Standard > Multiply
3. Enter: 1.1

Operations:

• Add
• Subtract
• Multiply
• Divide
• Percentage
• Percent Of

Statistics

Add % of total:

1. Select column
2. Transform > Statistics
3. Choose operation:
   • Sum
   • Average
   • Median
   • Min/Max
   • Count

Adds result as new column!

Date Transformations

Extract Date Parts

Get year from date:

1. Select date column
2. Transform > Date > Year

Creates new column with year only!

Available parts:

• Year
• Month (number or name)
• Day
• Quarter
• Week of Year
• Day of Week

Date Calculations

Days until today:

1. Add Column > Custom Column
2. Formula: Duration.Days(DateTime.LocalNow() - [DateColumn])

Age of transaction: Easy date math!

Conditional Columns

Create columns based on logic.

Simple IF

Add "High/Low" based on sales:

1. Add Column > Conditional Column
2. New column name: "Category"
3. IF [Sales] >= 1000 THEN "High"
4. ELSE "Low"

Multiple Conditions

Add "High/Medium/Low":

1. Add Column > Conditional Column
2. IF [Sales] >= 1000 THEN "High"
3. ELSE IF [Sales] >= 500 THEN "Medium"
4. ELSE "Low"

Click "Add Clause" for more conditions!

Custom Columns

Write your own formulas!

Basic Formula

Full Name from First + Last:

1. Add Column > Custom Column
2. Name: "FullName"
3. Formula: [FirstName] & " " & [LastName]

& is concatenation (join text)

Number Formula

Profit = Revenue - Cost:

1. Add Column > Custom Column
2. Name: "Profit"
3. Formula: [Revenue] - [Cost]

Using Functions

Uppercase first name: Formula: Text.Upper([FirstName])

Get year: Formula: Date.Year([OrderDate])

Common functions:

• Text.Upper(), Text.Lower()
• Text.Length()
• Number.Round()
• Date.Year(), Date.Month()

Unpivot Columns

Transform wide data to long format.

Wide format (bad for Power BI): Product | Jan | Feb | Mar Apple | 100 | 150 | 200 Orange | 80 | 90 | 110

Long format (good!): Product | Month | Sales Apple | Jan | 100 Apple | Feb | 150 Apple | Mar | 200 Orange | Jan | 80

How to Unpivot:

1. Select month columns (Jan, Feb, Mar)
2. Transform > Unpivot Columns

Result: Attribute (month) + Value (sales) columns!

Pivot Columns

Transform long to wide (opposite of unpivot).

When to use: Creating summary tables

How to Pivot:

1. Select column to pivot
2. Transform > Pivot Column
3. Choose values column
4. Choose aggregation (Sum, Count, etc.)

Group By

Create summaries.

Total sales by product:

1. Transform > Group By
2. Group by: Product
3. New column: TotalSales
4. Operation: Sum
5. Column: Sales

Creates summary table!

Append Queries

Stack tables vertically (union).

Combine Jan + Feb + Mar sales:

1. Home > Append Queries
2. Select tables to combine
3. Click OK

Tables must have same columns!

Merge Queries

Join tables (like SQL JOIN).

Add customer names to orders:

1. Home > Merge Queries
2. Select Orders table
3. Select CustomerID column
4. Select Customers table
5. Select CustomerID column
6. Choose join type:
   • Left (all orders)
   • Inner (matching only)
   • Right (all customers)
   • Full Outer (everything)
7. Expand new column to see customer data

Try This Exercise

Transform sample data:

1. Create table with:
   • FirstName, LastName
   • Sales amount
   • OrderDate
2. In Power Query:
   • Add FullName (custom column)
   • Add Year from OrderDate
   • Add Category (High/Low) based on sales
   • Round sales to whole number
3. Close & Apply

Best Practices

Clean early: Filter and remove columns at the start

Use descriptive names: Rename columns clearly

Test formulas: Preview results before applying

Avoid complex formulas: Break into multiple steps

Document: Right-click step > Properties > add notes

Next Steps

Now let's learn how to build proper data models with relationships!

Tip: Most transformations can be done in multiple ways. Choose what makes sense to you!