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:
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.
The Pivot dialog box will prompt you to enter the values columns. In this example, we will use Total Sales.
When you press OK the following pivoted result will appear.
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.
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.
Thus for our duplicate value, the Pivot result will be the SUM of the duplicates, no problem.
However, when the aggregation is set to Don’t Aggregate an error value will be returned for duplicates.
Don’t Aggregate result:
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 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.
The unpivoted result will be as follow:
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.