Power BI Intermediate Level: 31 - Transforming Columns Into Rows: UNPIVOT

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: Sometimes you encounter data where categories are spread out (pivoted) over several columns, for example you may have 12 columns corresponding to months. To properly analyze and plot the data, you need to transform columns into rows using the Unpivot operation in Power Query, e.g. so that you get a single column for the months. You can still display a pivoted representation of the data with a Matrix visual if needed.

Let us consider the following example: We have sales data for different stores over the months in a particular year, as shown below.

Now we want to plot the sales over the months. The source table is in a pivoted form, meaning that the individual months are all individual columns. This pivoted form is good for readability but bad for processing. For example, you cannot filter by month.

What we instead want is it to unpivot the columns with the individual months into a single Month column, like shown below. Note that we get fewer columns and more rows. The data is the same, just in a different shape that's more conducive to data processing. Then we can easily filter or use the single Month column as an axis in a line chart for example.

How can we achieve this in Power BI? Let’s check it out. Import the data in Power BI, then in the Power Query editor: (1) Select the Store column, which is the column you want to keep, and then (2) under the Transform ribbon, click the dropdown to the right of (3) Unpivot Columns, then select (4) Unpivot Other Columns.

This gives us the desired result. As final steps, rename the columns properly and change the type of column Month to a whole number.

Now with the input data transformed into the proper form, we can easily create a Line Chart with the transformed data.

For reading data, many people prefer the original pivoted form. You can have the best of both worlds by storing the data in unpivoted form (as above) and then create a Matrix Visual (equivalent of a Pivot Table in Excel) to display the data as a pivoted table.

In the next article, I will show you a set of useful transformation for when you have to header rows in your input data.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Processing Tables with Two-Row Headers