Scribd
Upload
3 views18 pages

Excel Power Query Column Transformations

The document provides an overview of column transformations in Power Query for Excel, emphasizing their importance in standardizing, enriching, and optimizing data for analysis. It outlines various types of transformations, including numeric, date/time, and text transformations, while addressing common misconceptions about the necessity and complexity of these processes. The content is aimed at helping users understand when and how to apply column transformations effectively.

Uploaded by

hadeer.mohamed.eldesokey
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views18 pages

Excel Power Query Column Transformations

The document provides an overview of column transformations in Power Query for Excel, emphasizing their importance in standardizing, enriching, and optimizing data for analysis. It outlines various types of transformations, including numeric, date/time, and text transformations, while addressing common misconceptions about the necessity and complexity of these processes. The content is aimed at helping users understand when and how to apply column transformations effectively.

Uploaded by

hadeer.mohamed.eldesokey
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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.

INTRODUCTION TO POWER QUERY IN EXCEL


Column transformations in the ETL journey

INTRODUCTION TO POWER QUERY IN EXCEL


Column transformations in the ETL journey

INTRODUCTION TO POWER QUERY IN EXCEL


Column transformations in the ETL journey

INTRODUCTION TO POWER QUERY IN EXCEL


When is column transformation needed?
Standardize and structure data.

INTRODUCTION TO POWER QUERY IN EXCEL


When is column transformation needed?
Standardize and structure data.
Data enrichment.

INTRODUCTION TO POWER QUERY IN EXCEL


When is column transformation needed?
Standardize and structure data.
Data enrichment.

Calculations.

INTRODUCTION TO POWER QUERY IN EXCEL


When is column transformation needed?
Standardize and structure data.

Data enrichment.

Calculations.

Storage optimization.

INTRODUCTION TO POWER QUERY IN EXCEL


Transformation types

INTRODUCTION TO POWER QUERY IN EXCEL


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).

INTRODUCTION TO POWER QUERY IN EXCEL


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).

INTRODUCTION TO POWER QUERY IN EXCEL


Text transformations
Splitting column contents.
Case conversion.

Concatenate text.

Calculate text length.

INTRODUCTION TO POWER QUERY IN EXCEL


Column transformations in Excel Power Query

INTRODUCTION TO POWER QUERY IN EXCEL


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.

INTRODUCTION TO POWER QUERY IN EXCEL


Let's practice!
INTRODUCTION TO POWER QUERY IN EXCEL
Transformation
types in Excel Power
Query
INTRODUCTION TO POWER QUERY IN EXCEL

Full Name
Performance Analytics Consultant
Let's practice!
INTRODUCTION TO POWER QUERY IN EXCEL

You might also like