Column Transformations

Introduction to Power Query in Excel

Lyndsay Girard

Performance Analytics Consultant

Overview

Column transformation: Modifying data in some way to meet your data analysis requirements.

Column transformations in the ETL journey

Column transformations in the ETL journey

Column transformations in the ETL journey

When is column transformation needed?

• Standardize and structure data.

When is column transformation needed?

• Standardize and structure data.
• Data enrichment.

When is column transformation needed?

• Standardize and structure data.
• Data enrichment.
• Calculations.

When is column transformation needed?

• Standardize and structure data.
• Data enrichment.
• Calculations.
• Storage optimization.

Transformation types

Numeric transformations

• Rounding values.
• Extract numeric information (e.g., even vs odd, sign).
• Calculate statistics (e.g., sum, count).
• Perform arithmetic operations (e.g., addition, multiplication, percentage calculations).

Date/time transformations

• Combine date/time fields.
• Extract date or time component.
• Round to start of the month, hour, etc.
• Date arithmetic (e.g., age calculation, duration calculations).

Text transformations

• Splitting column contents.
• Case conversion.
• Concatenate text.
• Calculate text length.

Column transformations in Excel Power Query

Common misconceptions

• Not every column needs a transformation!
• Data transformations can be reverted.
• Original source data remains intact.
• Advanced technical/coding skills are often not necessary.

Let's practice!

Introduction to Power Query in Excel