EClytics: Business Intelligence Services
How Can We Help?
< All Topics

Pivot and Unpivot Data in Power BI made easy

In Power BI, we can perform various data transformation such as changing column names, data types, pivot, un-pivot columns, deleting rows and columns, etc. in power query editor section of the Power BI.

What is Pivot and Unpivot?

Pivot is a process that aggregates the possible rows into single row in output.

Un-pivot data transform data from columns to rows where column heading are one row and rows of the data below the columns become their own separate, combined row.

How to Pivot and unpivot data in Power BI?


Let us consider the data is imported in Power BI as follows:

This data shows columns header as name of students, age, gender, subject and total marks. The rows corresponds to the actual records of each of the students. Now, we want to unpivot the column total marks.

We go to Transform > Unpivot Columns > Unpivot Only Selected Columns. So, we get the following output:

We can see total marks columns has been replaced by two columns i.e. Attribute and Value. Since, we unpivot only one column, so Attribute has only one column name and Values has all the values of the total marks as in the original data set. Also, number of rows is same as original data set.

Now, let us unpivot two columns age and total marks. Similarly, we go to Transform > Unpivot Columns > Unpivot only selected columns. We get the following output:

Here, the Attribute column has two column names, i.e. total marks and age and their Value corresponds to all the values of age and total marks in the original data set. The number of rows is also double the number of rows in the original data set. Similarly, if we unpivot three columns then the number of rows in the output will be three times.

PIVOT DATA: To pivot the data, reverse process is performed. With the same data as in the above output, go to Transform > Pivot columns.

The following output is available:

That’s all folks! Thanks for reading! Check out more articles of Power BI on our blog!

Add comment

Table of Contents