Power Query Unpivot

Power Query’s Unpivot tool allows us to fix one of the most common mistakes (many) Excel users make when capturing data in Excel, and that is to use the wrong layout.

You see they tend to jump into a report format, or they might put it in a format that makes it easy for someone to input the data. The problem with this is it makes it very difficult to then use the built-in tools available, tools like PivotTables and formulas.

Here’s an example of the wrong layout (don’t be fooled by it’s neat and tidy appearance, it’s evil):

“What’s wrong with this layout”, I hear you say. Well this layout is already Pivoted, in other words this layout is something a PivotTable can produce in seconds. It’s the end result, as opposed to the way you should capture data. Ok, maybe ‘evil’ is a bit harsh, but it’s going to cause you a lot of pain and anguish.

Here’s an example, let’s say you send this report to your boss. Then five minutes later she comes back and says “that report is great, but can you also show me the data grouped by Salesperson by quarter …. Oh, and I need it in 10 minutes for a meeting”.

Right about now you agree with me that this layout is evil. You’ll be lucky to turn this around in ten minutes.

Hold up, before you panic let’s look at a different scenario. What if you had your data in the ideal tabular format like this:

In less than 2 minutes you could create both of your reports from this perfect tabular data using PivotTables. Here they are:

Oops, boss said she wants Salesperson in the rows and periods in the columns. No problem, you can just drag and drop the fields in the PivotTable field list to switch them around.  A few seconds later and here it is:

Now, I’m not going to continue with my rant about the right layout for your data, I do that here. Instead I want to show you how you can use Power Query to easily unpivot your data so that it’s in a nice, friendly tabular layout.

Download the Workbook

Power Query Unpivot

Note: Power Query is available for Excel 2010 onwards. Excel 2010 and 2013 users see system specifications and download it here. For Excel 2016 users, Power Query is already available from the Data tab in the Get & Transform group:

Unpivoting data using Power Query is easy.

Step 1: Select your data, in my case it’s in cells A1:Z18

Step 2: Excel 2010 and 2013; on the Power Query tab > From Table. Make sure the range is correct and check the box ‘My table has headers’ (assuming yours does too):

Step 2: Excel 2016; Data tab > From Table:

This will open the Query Editor.

Tip Before moving on, it's a good idea to remove the automatically inserted 'Changed Type' step as this can sometimes cause problems later on. Just click the 'X' to remove it before moving on to Step 3.

Step 3: In the Power Query editor, which is the same in all versions of Excel, we want to unpivot the columns that contain values. Since we only have a couple of columns that don’t need unpivoting I’ll select them (hold down CTRL and click the Country and Salesperson columns) > Transform tab > Unpivot Columns > Unpivot Other Columns:

It should look like this with the column headers now in the ‘Attribute’ column and the Sales Amounts in a ‘Value’ column:

Tip: if you expect to add new columns to your source data, for example figures for a new month, then choosing ‘Unpivot Other Columns’ will ensure any new columns are automatically included when you refresh the query.

Step 4: Double click the ‘Attribute’ and ‘Value’ column headers and enter more appropriate names:

Step 5: If you scroll down you’ll see we have the Total rows still in the data. We can use the filter button on the Salesperson column to filter out all rows that contain ‘null’, which is the equivalent of a blank cell:

The great thing about Power Query is it’s a bit like a Macro recorder in that it records each step as you go. We can see them in the ‘Applied Steps’ list on the right-hand side of the query editor:

This means that if we update the source data with figures for a new month we can simply click the ‘Refresh’ button on the Data tab and Power Query will get the new data and run it through all the steps without us having to make any changes.

Step 6: We also have a total row in the month column that needs filtering out, so just like the previous step; select the filter button on the Month column > scroll to the bottom and deselect ‘Total’:

Step 7: It’s always best practice to ensure dates are stored in the correct format in Excel. We can see that the Month column has the Data Type: Text, which will not play nice with PivotTables or formulas.

We can tell this from the Home tab > Data Type is ‘Text’ and the indicator on the column header is ‘ABC’, which means Text:

To fix this, select the ‘Month’ column > Home tab > change the Data Type to ‘Date’:

Note: if you want to practice this using my sample workbook and your date format is mm/dd/yyyy, then you need to change the date type using the Locale. To do this right-click the Month column > Change Type > Using Locale > Data Type: Date and Locale: English (Australia).

Now the Month column has the calendar icon in the header, the dates look like real dates and they’re right aligned:

Step 8: Name the query; let’s give the query a meaningful name, rather than the default table name.

This will help us identify the data more easily when we reference it in our PivotTables and formulas. Simply type over the name in the Properties Name field in the right-hand side of the query editor:

Step 9: Close & Load: we’re ready to load the data into Excel. Home tab > Close & Load:

This will put your data in an Excel Table called ‘Sales_Data’ in a new worksheet in your file, and the Workbook Queries pane will open on the right:

Now you’re ready to analyse and summarise your perfect tabular data with PivotTables, or formulas to your heart’s content.

More Power Query

Click here for more Power Query tutorials.

And if you want to get up to speed quickly please check out my Power Query course.