Pivot and Unpivot walkthrough in Power BI

Power Query in Power BI provides very effective functionality to pivot and unpivot columns. For the pivot functionality you turn rows to columns and for the unpivot functionality the inverse is true where columns are transformed into rows.

Power BI Pivot

We will be working with the following data set:

Power BI: Pivot and Unpivot

We would like to transform the months, currently rows to columns and aggregate the Total Sales, the value column, per month. This means that we will have a column per month. To do this go into Power Query, select Transform and then select Pivot Column.

Power BI: Pivot and Unpivot

The Pivot dialog box will prompt you to enter the values columns. In this example, we will use Total Sales.

Power BI: Pivot and Unpivot

When you press OK the following pivoted result will appear.

Power BI: Pivot and Unpivot

You will notice that we have a column for each month. This is very valuable, you will also see that the rows have been grouped by the years. So important to note here is that Pivot requires a name column, in our case Month, and a value column, in our case Total Sales.

Duplicate Values

Something to look out for and understand is duplicate values. In our example, we have duplicate values for July 2018. Note here that the default aggregation of the Pivot function is SUM.

Power BI: Pivot and Unpivot

Thus for our duplicate value, the Pivot result will be the SUM of the duplicates, no problem.

Power BI: Pivot and Unpivot

However, when the aggregation is set to Don’t Aggregate an error value will be returned for duplicates.

Power BI: Pivot and Unpivot

Don’t Aggregate result:

Power BI: Pivot and Unpivot

This is quite an important element to be aware off as this might easily return incorrect results if source data was not cleaned properly. If you expect the data to have duplicates then you need to just select your aggregation carefully. If you don’t expect duplicate values in your dataset then it is best to not aggregate your data as this would be an easy way to detect if there are any unnecessary duplicates in your data.

Unpivot

Unpivot produces the opposite result than what we just experienced with Pivot.  Unpivot will convert you column name into one column into rows and your values into another column. Let’s test this with the same dataset we used for the Pivot function. You must select the column that you would like to Unpivot, in our case the year’s column. When unpivoting select Unpivot other columns.

Power BI: Pivot and Unpivot

The unpivoted result will be as follow:

Power BI: Pivot and Unpivot

You will note here that the columns and their values were split between two columns namely attribute and value.

That is it for this post. Pivot and Unpivot are quite simple, but very useful functions to know how to use properly.

View our blog page for more.  Visit our training page,  to learn more.