This post may contain affiliate links. Please read my disclosure for more info.

Power Query – Unpivot Data

Power Query - Unpivot Data

Preparing data is probably the most critical success factor in data analysis.  If the data layout is wrong, you be continually fighting Excel; having to use advanced formulas, adding unnecessary columns or even duplicating data.  But if the data is in the right structure, Excel becomes easy.  I used to be amazed at people who could use complex formulas.  I picked up a lot of tips and tricks by trying to learn these same techniques.  But I have found that getting data in the correct format I have found these advanced skills are rarely needed.

So what is the correct layout data for Excel to use?  Check out this post which provides a good answer to that question.

Before Power Pivot, it would take us a long time to transform data; we had to resort to complexity or long VBA codes.  But Power Query has many features to make this transformation easy.  One of the most useful features is unpivot, which is what we are covering in this post.  Over three examples, we will progress from easy to tricky source data formats.  But it will become clear that a grounding in a few basic transformations used in the right way is all it takes.

Downloads

To work along with the examples below, download the sample files.  Click here, to subscribe and get access to the Downloads section.

Excel Downloads

The examples in this post use the file called Example 12 – Unpivot Data.xlsx

Now get Excel fired up, open the example file.  We are ready to go!

Basic unpivot

For our first go at unpivot, we are using the Example 1 worksheet.

Example 1 Data

The source data displays a separate column for each month.  However, rather than having a column for each month, it is better to have a column containing the month name as an attribute along with single value column.  This is the data layout we are aiming for by the end of this example.

Select any cell in the data range and change it into an Excel Table by click Insert -> Table (or pressing Ctrl + T)

Insert Table - Ribbon

The Create Table window will guess the data range and if there is a header row.  The source information includes a Total in row 42, which we can exclude this from the data range.

Since our data has a single header row, we can select the My table has headers option, then click OK.

Create Table Window

Click on any cell in the Table, the Table Design window ribbon will appear.  Give the Table a useful name (I have gone for SalesData).

Rename Table

Now we are ready to load the SalesData Table into Power Query.  Click Data -> From Table/Range.

Data from Table or Range

The Power Query Editor opens showing a preview of the data.

Preview Window - Unpivoted

If at any point in the future there could be more columns or different columns, then delete the Change Type step.  This step hardcodes the column headers into the M code, so will not automatically expand or retract for different columns layouts.

OK, now for the unpivoting to happen.  Within the Transform -> Unpivot Columns button there are three options, the option we choose depends on the outcome we want.

Unpivot options

From those three options, there are really only two outcomes; we can unpivot on selected or unselected columns.  What is the difference, and is it important?

If we were to select the January, February and March column and apply the Unpivot Only Selected Columns, it would unpivot on those columns.  Power Query creates a new column entitled Attributes containing the months.

Preview Windows - Unpivoted Selected Columns

The M code to achieve this transformation would be:

= Table.Unpivot(#"Changed Type", {"January", "February", "March"}, "Attribute", "Value")

The column headers of January, February and March are hardcoded into the M code.  So what happens if we add additional data for April?  Power Query will not unpivot the April column as it is not included in the code.  But if you’re likely to add a new attribute column then it will work just fine.

Instead, we could select the Customer and Product columns and use the Unpivot Other Columns option.  For our current dataset, it achieves the same visual result in the Preview Window, but the M code is different.

= Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "Product"}, "Attribute", "Value")

The code does not reference the month columns; therefore any additional months added will also be unpivoted.  This is great for data columns, but not for attribute/dimension columns.

Now tidy up the query with the following transformations:

  • Change the header of the Attribute column to Months.
  • Change the data type of each column to fit with the data within the column (I’ve gone for Text, Text, Text, Decimal Number).
  • Filter to remove the zeros from the Value column.

Click Close & Load to push the data into Excel.  The worksheet will look like this:

Unpivot Close and Load - Example 1

From this, we can use formulas or Pivot Tables to create any view we want.  This is the power which data preparation gives us.

Unpivot with multiple columns

Now it’s time for Example 2.  The source data looks like this:

Example 2 Data

Instead of one column for each month, we now have two, showing sales  Values and sales Units.  The most useful format is to have the month names in a column with the Value and Units as separate columns.

The initial steps are the same as Example 1:

  • Convert the data into a Table (use the first row as the header and exclude the total from the range).
  • Give the Table a useful name.
  • Click Data -> From Table/Range to load the data into Power Query.
  • Just to be safe, delete the Change Type step which was created by Power Query automatically.

The Preview Window should be as follows:

Example 2 - Preview Window

Now we need to make quite a few transformations.

  • Select the Customer and Product columns and click Transform -> Unpivot Columns (dropdown) -> Unpivot Other Columns
  • Select the Attribute column and split the column into the parts we need.
    For our example, we need to split by the space character.  Click Transform -> Split Column ->  Split by Delimeter.  Then apply the settings below.
    Split by delimeter window
  • The Attribute column will now be split into two separate columns.
  • Select the column which displays Values and Units, then click Transform -> Pivot Column
  • In the Pivot Column window select column containing the numbers, then click OK.
    Pivot Column Window Settings
  • Change the names of the Attributes column to Months.
  • Set the data type for each column.

Finally, click Close & Load.

The unpivoted data in Excel now looks like this… perfect!

Close and Load for Example 2

Unpivot with multiple header rows

Now our final example.  Everything is the same as Example 2, except for the table headers.  We now have two header rows, the first showing months and the second showing Value or Units.

Example 3 Data

Excel Tables and data in Power Query can only have a single header row.  This is good data management practice.  By separating the headers into two rows, it becomes a presentational format which is very difficult to work with in Excel.

Let’s start by turning this data into an Excel Table.  There is one crucial difference to the previous examples before, do not select the MyTable has Headers option.

Create Table without Headers

After clicking OK,  Some significant changes will have been made to the source data.

  • A header row showing Column1, Column2, Column3, etc. has been added
  • All the rows have been moved down by one row to make space for the new header row
  • The merged cells have been unmerged, with the content placed into the first cell of each merged area.

Table for Example 3

Import the data into Power Query by selecting any cell in the Table, then clicking Data -> From Table/Range.

The Preview Window shows the following:

Preview Window - Month not in each column

This view gives us a problem; it doesn’t show the month in every column.  If we try to unpivot, it will unpivot the null value.  We first need to use the Fill Across transformation… but there isn’t one!  Instead, we need to transpose the data, Fill Down, make some transformations, then transpose back again.

  • Click Transform -> Transpose
  • Select Column1, click Transform -> Fill (drop-down) -> Down
  • Select Column1 and Column2
  • Click Transform -> Merge Columns
  • In the Merge Columns window choose a separator which is not in the text of the two columns already (I have gone for a space, but this might not be the best option for your circumstances)
  • Click Transform -> Transpose to return the data back to its previous format
  • Select Column 1 and Column 2.
  • Click Transform -> Format (dropdown) -> Trim to remove the space character from the Customer and Product columns we added in a previous step
  • Now promote the first row as the header by clicking Transform -> Use First Row as Headers

After all those transformations the Preview Window will look like this:

Preview Window - Example 3 after initial transformations

It’s now the same format as we had in Example 2.  To complete this example, follow through all the transformation steps from that example.

 



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *