Data Transformation & Cleaning with Power Query0%

Merging and Appending Queries (Joins & Unions)

Data Transformation & Cleaning with Power Query

Beginner12 min read•Updated: Jul 11, 2026

Data Transformation & Cleaning with Power Query

Raw data is rarely analysis-ready. It arrives with missing headers, mixed data types, unwanted columns, and messy pivoted layouts. In the Power Query Editor, you can reshape and sanitize data with intuitive point-and-click tools.

1. Essential Everyday Transformations

In the Power Query Editor Ribbon:

Use First Row as Headers: Promotes row 1 into official column headers (found under the Home tab).
Remove Columns: Highlight unnecessary columns and click Remove Columns (or right-click > Remove Other Columns to protect against unexpected new columns).
Change Data Types: Click the icon in the column header (e.g., 'ABC' for text, '123' for integer, '$' for currency, calendar for date) to enforce strict schema types.
Replace Values: Right-click a column > Replace Values... to replace "N/A" or "NULL" with '0' or blank.
Split Column: Split by delimiter (comma, space, custom character) or by number of characters.

2. The Game Changer: Unpivot Columns

One of Power Query’s most celebrated superpowers is Unpivoting. Standard business reports often present data in a "wide" format (e.g., Months spread across columns: Jan, Feb, Mar... Dec):

1The Problem (Wide Format - Terrible for Pivot Tables):
2+------------+---------+---------+---------+
3| Product    | Jan     | Feb     | Mar     |
4+------------+---------+---------+---------+
5| Laptops    | 100     | 120     | 140     |
6+------------+---------+---------+---------+
7 
8The Solution (Unpivoted Normalized Format - Perfect for Analysis):
9+------------+---------+---------+
10| Product    | Month   | Sales   |
11+------------+---------+---------+
12| Laptops    | Jan     | 100     |
13| Laptops    | Feb     | 120     |
14| Laptops    | Mar     | 140     |
15+------------+---------+---------+

How to Unpivot in 2 Clicks:

1
Highlight the non-date columns (e.g., 'Product' and 'Region').
2
Go to the Transform tab.
3
Click the dropdown arrow on Unpivot Columns > choose Unpivot Other Columns.
4
Excel normalizes the wide monthly headers into two tidy columns: Attribute (Month) and Value (Sales)!

3. Adding Custom and Conditional Columns

Conditional Column (Add Column Tab): Works like a visual IF-THEN-ELSE builder:
If [Sales] is greater than 100,000, then "High", Else "Standard".
Custom Column: Write expressions using the M language:

1  [UnitPrice] * [Quantity] * (1 - [Discount])

Column From Examples: Type what you want the output to look like, and Power Query's pattern engine infers the transformation automatically!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the 'Unpivot Other Columns' command accomplish in Power Query?

Question 2

How can you promote raw data row 1 to become official column headers in Power Query?

Question 3

What does clicking 'Remove Other Columns' do compared to 'Remove Columns'?

Question 4

Which tab in the Power Query Editor allows you to generate new columns using visual IF-THEN rules?

Question 5

If you make a mistake while cleaning data in Power Query, how do you undo it?

Next Lesson

Merging and Appending Queries (Joins & Unions)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Power Query Overview & Importing Data from Multiple Sources	Merging and Appending Queries (Joins & Unions)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Merging and Appending Queries (Joins & Unions)

Data Transformation & Cleaning with Power Query

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Data Transformation & Cleaning with Power Query

1. Essential Everyday Transformations

In the Power Query Editor Ribbon:

Use First Row as Headers: Promotes row 1 into official column headers (found under the Home tab).
Remove Columns: Highlight unnecessary columns and click Remove Columns (or right-click > Remove Other Columns to protect against unexpected new columns).
Change Data Types: Click the icon in the column header (e.g., 'ABC' for text, '123' for integer, '$' for currency, calendar for date) to enforce strict schema types.
Replace Values: Right-click a column > Replace Values... to replace "N/A" or "NULL" with '0' or blank.
Split Column: Split by delimiter (comma, space, custom character) or by number of characters.

2. The Game Changer: Unpivot Columns

One of Power Query’s most celebrated superpowers is Unpivoting. Standard business reports often present data in a "wide" format (e.g., Months spread across columns: Jan, Feb, Mar... Dec):

1The Problem (Wide Format - Terrible for Pivot Tables):
2+------------+---------+---------+---------+
3| Product    | Jan     | Feb     | Mar     |
4+------------+---------+---------+---------+
5| Laptops    | 100     | 120     | 140     |
6+------------+---------+---------+---------+
7 
8The Solution (Unpivoted Normalized Format - Perfect for Analysis):
9+------------+---------+---------+
10| Product    | Month   | Sales   |
11+------------+---------+---------+
12| Laptops    | Jan     | 100     |
13| Laptops    | Feb     | 120     |
14| Laptops    | Mar     | 140     |
15+------------+---------+---------+

How to Unpivot in 2 Clicks:

1
Highlight the non-date columns (e.g., 'Product' and 'Region').
2
Go to the Transform tab.
3
Click the dropdown arrow on Unpivot Columns > choose Unpivot Other Columns.
4
Excel normalizes the wide monthly headers into two tidy columns: Attribute (Month) and Value (Sales)!

3. Adding Custom and Conditional Columns

Conditional Column (Add Column Tab): Works like a visual IF-THEN-ELSE builder:
If [Sales] is greater than 100,000, then "High", Else "Standard".
Custom Column: Write expressions using the M language:

1  [UnitPrice] * [Quantity] * (1 - [Discount])

Column From Examples: Type what you want the output to look like, and Power Query's pattern engine infers the transformation automatically!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the 'Unpivot Other Columns' command accomplish in Power Query?

Question 2

How can you promote raw data row 1 to become official column headers in Power Query?

Question 3

What does clicking 'Remove Other Columns' do compared to 'Remove Columns'?

Question 4

Which tab in the Power Query Editor allows you to generate new columns using visual IF-THEN rules?

Question 5

If you make a mistake while cleaning data in Power Query, how do you undo it?

Next Lesson

Merging and Appending Queries (Joins & Unions)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Power Query Overview & Importing Data from Multiple Sources	Merging and Appending Queries (Joins & Unions)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next