Transforming Tables in Microsoft Power BI: Pivot and Unpivot

Pivot and Unpivot columns are ways of transforming tables in Microsoft Power BI. PIVOT turns rows into columns and UNPIVOT turns columns into rows.  

According to Microsoft Learning Page, PIVOT columns could be defined as:

In Power Query, you can create a table that contains an aggregate value for each unique value in a column. Power Query groups each unique value, does an aggregate calculation for each value, and pivots the column into a new table.

Although we can choose to not aggregate (that is not a good option in the case of having duplicates), PIVOT implies an aggregation. In the advanced options, we have the field aggregate value function with the options: count (all), count (not blank), minimum, maximum, median, don’t aggregate. 

On the other hand, to UNPIVOT, and still according to the same page of Microsoft:

In Power Query, you can transform columns into attribute-value pairs, where columns become rows.

In UNPIVOT, we could unpivot columns, unpivot other columns and unpivot only selected columns.  The result is a matrix. 

To PIVOT or to UNPIVOT columns, we need to be in the Power Query Interface/Editor, ‘Transform’ menu, as shown in the following image.