Unpivot Columns in Excel with the Amazing Power Query

In this blog post, we look at how to unpivot columns in Excel. The incredible Unpivot Columns feature of Power Query makes it quickly and effortless to unpivot data.

Power Query allows us to transform messy data into something meaningful and useful. And better yet, it is quick and easy to do so.

The Unpivot Columns tool within Power Query is one of its greatest assets. So lets have a look at an example of its use.
Our mission is to convert the table on the left, into the table on the right in the image below.

The table on the left is a typical example of data that we may receive when downloading or exporting out of a system. It is already in a pivot format. It looks neat, but is not very useful.

We want the data in a tabular format so that we can easily analyse it further using Excels power. For example, in our own PivotTable.

Watch the Video

Unpivot Columns in Excel with Power Query

To use Power Query, the range of cells should be formatted as a table. At the moment, the data on my sheet is not.

This is ok, because Excel will convert it into a table for me as part of the process. It is however encouraged to already be in a table. In fact when you download or import data, Excel typically puts it into a table immediately.

This process is demonstrated using Excel 2016. The steps may be slightly different in your version of Excel.

Click on a cell within the table, or range of cells.

Click the Data tab, and then the From Table/Range button in the Get & Transform group.

The Create Table window appears to confirm the range with me, before it is then converted into a table format.

The table is then loaded into Power Query and the Query Editor window is opened.

The empty cells from the range are shown as null values and the city names appear as headers.

It is a good idea to name the query first. In the Query Settings pane on the right, enter Sales as the name of the query (This will be the table name when it is loaded back into the worksheet).

We will then select the columns that we want to unpivot. In this example, I will select the Boston column (first column), hold down the Shift key of the keyboard and select the Paris column (last column).

Click on the Transform tab, and then the Unpivot Columns button.

Now a really interesting thing here is that the Unpivot Columns button actually unpivots all the unselected columns.

What this means is, although I selected 4 columns (Boston, Tokyo, London and Paris), the feature is focusing on what I did not select.

So if we have more, or fewer, cities in our data range in the future. Power Query will successfully unpivot all of them.

This is exactly what we want in this scenario. However, if we did specifically mean those 4 columns, the list next to the Unpivot Columns button does provide alternative approaches.

After clicking the button, the 4 columns are successfully unpivoted. We now have a table with each row as a sale. The null values have been omitted.

We can now rename the column headers. Double click on the Attribute header and rename it City. Then double click on the Value header and name it No of Sales.

Each step is added to the Applied Steps on the right.

We can now load this to the worksheet. Click on the Home tab and then the Close & Load button.

In the future, is the data in the original range is changed or added to. We can simply click Data and then Refresh to apply the Power Query steps again and get up to date data.

So the real beauty of this feature not only lies in its simplicity, but also that we can do it again with the click of a button in the future.

Power Query is an incredible tool and can make transforming data in Excel easy. The ability to easily unpivot columns in Excel with the Unpivot Columns button is one of the most loved buttons in the Power Query toolset.