Understanding about The Pivot, Unpivot and Transpose

[ajaybabuinturi]

While developing a BI report, sometimes information is spread across multiple columns and other times it is packed into rows. There are three powerful Power Query transformations come into the picture, which are Pivot, Unpivot and Transpose.

In this blog, l will walk you through what they are, why they are useful and how they different.

 

1.Unpivot | When You Need to Make Data Ready for Analysis

What is Unpivot?

The Unpivot transformation is used when your data has repeating headers spread across columns. It converts those column headers into rows, which provides you a more flexible analysis and normalized structure.

Why it is Useful?

In Power BI visuals especially time-based visuals, works best when time periods like months or years are in rows rather than columns. Unpivoting helps you turn wide data into tall data by making it easier to slice, filter and aggregate.

Example:

Let’s say, you have a sample data as provided below.

This layout looks fine in Excel spreadsheet but it is not suitable for Power BI analysis. In this case, Unpivot helps convert the dataset into a well-structured and normalized format. 

• Go to Transform tab --> Select Product column --> Unpivot dropdown --> Unpivot Other Columns --> Rename the Unpivoted Column Names or Go to Transform tab --> Select all Months column --> Unpivot dropdown --> Unpivot Only Selected Columns --> Rename the Unpivoted Column Names.

• Now the below data is ready for analysis which ensures flexible and easy to develop report.

 

2. Pivot | When You Need to Summarize Data

What is Unpivot?

Pivot does the exact opposite of Unpivot. It takes values from rows and turns them into columns based on a key field.

Why it is Useful?

You will often receive data where each product or entity appears multiple times for different metrics. Pivoting helps to consolidate those rows into a single record per entity, making data easier to read and helps easy reporting.

Example:

Let’s say, you have a sample data as provided below.

• Go to Transform tab --> Select Metric column --> Pivot --> Select Values column name from dropdown --> OK

• This below summarised structured data is ideal for combining KPIs in a single dataset.

 

3. Transpose | When You Need to Flip the Entire Table

What is Transpose?

Transpose switch your entire table, rows become columns and columns become rows. It helps when your dataset is oriented horizontally, often seen in raw exports or manually prepared files.

Why it is Useful?

Transpose is perfect for restructuring tables where each column represents a separate record. It is less about analysis and more about correcting structural issues before finalise the data.

Example:

Let’s say, you have a sample data as provided below.

• Go to Transform tab --> Column1 (where your fields are resided) --> Transpose --> Use First Row as Headers and remove useful columns.

• After applying Transpose, the table looks like below. Now each supplier has proper row and ready for relationships or lookups.

 

Understanding the Differences:

Practical Tips:

• Always use Unpivot when you have repeated headers like months or regions.
• Use Pivot when you want to create a summarized version of multiple metrics.
• Apply Transpose carefully, it’s powerful but can make columns incompatible with model relationships.
• After any transformation, always check data types.

Data transformation is one of the most important skills in Power BI. Before you even start writing DAX or building visuals, ensuring your data is shaped correctly will save you hours of troubleshooting.

Pivot, Unpivot nd Transpose.pbix

[AbdulBasha]

Good Information 

[NagaSuresh]

Thanks for Sharing @ajaybabuinturi 

[suwarnamalaw]

• Very well explained, thank you for sharing the concept in simple language.