Power Query: Which Way to Flip

Welcome to our Power Query blog. This week, I look at transposing versus unpivoting data.

It can be confusing when deciding how to manipulate data, particularly when the options may sound similar:

• Transposing data: this essentially treats rows as columns and columns as rows. The original column names are not used, so transposing a table twice just removes the column names and leaves generic column headings such as ‘Column1’
• Unpivoting data: this takes any selected column and translates it into attribute-value pairs. Therefore, if I have a column headed ‘Tents’ with Small, Medium and Large as values, I will get two columns, one with Tents as every value (the Attribute column) and one with the Small, Medium and Large values. The column names are not preserved. If I unpivot twice, then I get a new attribute column with ‘Attribute’ and ‘Value’ as the value and a new value column with all the values from the previous columns, i.e. Tents, Small, Tents, Medium, Tents, Large.

What I need, is an example!

I have some tent data, but it’s not in a table format. I need to start by extracting my data to Power Query by using ‘From Table’ on the ‘Get & Transform’ section of the Data tab.

I don’t click that my data has headers, as that is not how the data is currently organised.

I start by transposing my data. I can do this from the Transform tab.

This has sorted out my first two columns, which now contain the supplier and tent type data respectively. The order numbers are all along the top row, which is not what I want. I need to unpivot my order data. First, however, I need to get my order numbers into the column headings, as I know that unpivoting will create a column of my headings next to the values.

I use ‘Promote first Row as Headers’ from the Transform tab.

I have my order data ready to unpivot, and I need to select all the columns with order numbers and the Order Number column, and unpivot them from the Transform tab:

It’s quicker to choose the first two columns and then unpivot the rest.

I can now fill down the Supplier column by right-clicking and choosing ‘Fill’ and then ‘Fill Down’.

I rename my headings and change the type on my currency column to finish my table.

From this example, I notice that before transposing, I must make sure that no data I want is in the headings, as I will lose it. Conversely, if I unpivot, the data in the headings is used, so before unpivoting I need to check the data that I want to appear as a column is in the headings.

Come back next time for more ways to use Power Query!