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:

After pivoting “Year”:

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:

After unpivoting:

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

Becomes:

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

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.