This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Pivot, Unpivot, and Transpose Data

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

---

Real-world datasets often come in formats that are not ready for analysis or visualization. The ability to reshape data by pivoting, unpivoting, or transposing columns and rows is a fundamental skill for transforming data into the correct structure for modeling.

This capability resides in Power Query Editor, and the PL-300 exam tests both your conceptual understanding and practical decision-making skills in these transformations.

---

Why Reshape Data?

Data can be presented in a variety of layouts, including:

• Tall and narrow (normalized)
• Wide and flat (denormalized)
• Cross-tab style (headers with values spread across columns)

Some visuals and analytical techniques require data to be in a normalized (tall) format, while others benefit from a wide format. Reshaping data ensures that:

• Tables have consistent column headers
• Values are in the correct place for aggregation
• Relationships and measures work properly
• Models are efficient and performant

---

Where Pivoting, Unpivoting, and Transposing Occur

All three transformations happen in Power Query Editor:

• Pivot Columns
• Unpivot Columns
• Transpose Table

You can find them primarily in the Transform or Transform → Any Column menus.

Exam tip: Understanding why the transformation is appropriate for the scenario is more important than knowing the exact UI path.

---

Pivoting Columns

What It Does

Pivoting converts unique values from one column into multiple new columns.
In essence, it rotates rows into columns.

Example Scenario

A dataset:

Product	Year	Sales
A	2023	100
A	2024	120

After pivoting “Year”:

Product	2023	2024
A	100	120

When to Use Pivot

• You need a matrix-style layout
• You want to create a column for each category (e.g., year, region, quarter)

Aggregation Consideration

Power BI may require you to provide an aggregation function when pivoting (e.g., sum of values).

---

Unpivoting Columns

What It Does

Unpivoting converts columns back into attribute–value pairs, essentially turning columns into rows.

Example Scenario

A wide table:

Product	Jan	Feb	Mar
A	10	15	20

After unpivoting:

Product	Month	Sales
A	Jan	10
A	Feb	15
A	Mar	20

When to Use Unpivot

• Your data has repeating columns for values (e.g., months, categories)
• You need to normalize data for consistent analysis

Exam Focus

Unpivot is one of the most frequently tested transformations because real-world data often arrives in a “wide” layout.

---

Transposing a Table

What It Does

Transposing flips the entire table, making rows into columns and columns into rows.

Example

A	B	C
1	2	3
4	5	6

Becomes:

Column1	Column2
A	1
B	2
C	3
(next)	4
…	…

When to Use Transpose

• The dataset is oriented incorrectly
• The first row contains headers but is not in column form
• You’re reshaping a small reference table

Important Note

Transpose affects all columns — use it when the entire table must be rotated.

---

Common Patterns in the PL-300 Exam

The PL-300 exam often tests your ability to recognize data shapes and choose the correct approach:

Scenario: Suboptimal Layout

A dataset has months as column headers (Jan–Dec) and needs to be prepared for a time-series analysis.
Key answer: Unpivot columns

Scenario: Create a Cross-Tab Summary

You want product categories as columns with aggregated values.
Key answer: Pivot columns

Scenario: Fix Improper Orientation

The first row contains headers and the current format is not usable.
Key answer: Transpose table (often followed by promoting the first row to headers)

---

Best Practices (Exam-Oriented)

• Understand the shape of your data first: Diagnose whether it’s tall vs wide
• Clean before reshaping: Remove nulls or errors so the transformation succeeds
• Group/aggregate after unpivoting when necessary
• Use “Unpivot Other Columns” when you want to keep important keys and unpivot everything else
• Pivot only when categories are fixed and small in number (too many pivot columns can bloat the model)
• Transpose sparingly — it’s usually for reference tables, not large fact tables

Know when not to pivot: Don’t pivot if it will produce too many columns or if the data is already in normalized format suitable for analysis.

---

Impact on the Data Model

Your choices here affect:

• Model shape and size: Too many columns from pivoting can bloat the model
• DAX flexibility: Normalized (unpivoted) tables support richer filtering and relationship behaviors
• Performance: Unpivoted fact tables often perform better for filters and slicers

Choose wisely whether the transformation should occur in Power Query (Physically reshape the data) or via a visual/DAX technique after loading.

---

Common Mistakes (Often Tested)

The exam often presents distractors like:

❌ Mistaking Pivot for Unpivot

Students try to pivot when the scenario clearly describes normalizing repeated columns.

❌ Transposing without Promoting Headers

Transpose alone doesn’t fix header issues — often you must promote the first row afterward.

❌ Pivoting Without Aggregation Logic

Pivot requires defining how values are aggregated; forgetting this results in errors.

❌ Unpivoting Key Columns

Using unpivot incorrectly can duplicate keys or inflate the dataset unnecessarily.

---

How This Appears on the PL-300 Exam

Expect scenario-based questions like:

• “Which transformation will best convert this wide-format month columns into a single Month column?”
• “The first row contains field names that should be column headers — what is the correct sequence of transformations?”
• “Which transformation will turn categories into columns for a matrix visual?”

Answers are scored based on concept selection, not clicks.

---

Quick Decision Guide

Scenario	Best Transformation
Multiple value columns need to become rows	Unpivot
One column’s values need to become individual columns	Pivot
Entire table needs rows/columns flipped	Transpose

---

Final Exam Takeaways

• Pivot, unpivot, and transpose are powerful reshape tools in Power Query
• The exam emphasizes when and why to use each, not just how
• Understand the data shape goal before choosing the transformation
• Cleaning and data type correction often precede shaping operations

---

Practice Questions

Go to the Practice Exam Questions for this topic.