When you have a tool that can carry out up to 380 data transformations, mostly through simple GUI clicks, then the tool is not ordinary.
That explains why Power Query, originally born in Excel, is now a powerful engine in many other tools (Power BI, Power Automate, CDS, Azure Analysis Services, may be many more to come). Once a user understands How to Clean Dirty Data, there is a 99.9% chance of finding all the buttons that will take data from Dirty to Clean in Power Query.
The GUI is packed with a lot of transformational buttons. I am not sure I have used them all in my many years of using Power Query. Some of them though, are more frequently used than others, like the Pareto Principle, the 80/20 Rule. The Unpivot and Transpose buttons fall within that category of 20.
When you Transpose a table, the physical occurrence is the conversion of items on rows to columns and items on columns to rows respectively.
When you Unpivot, the physical occurrence seems like the conversion of items on rows to columns. In fact, when you unpivot, the headers become items lined in a single column and every item belonging to each header forms another column in front of that.
Let’s look at a dataset that requires scenarios for Transpose and Unpivot to drive home the lessons and the points to note about these two features.
We have a table looking like the one below:
Instead of looking like the one below:
The difference here is the fact that Ship Mode and Segment are supposed to be arranged in two different columns with their amount values in front of them on a separate column. Instead, we have them arranged in two different rows.
It’s obvious we need to transpose things. Of course, Transpose alone won’t solve this problem.
Let’s take this data to Power BI and see how we can resolve this issue.
When the data is imported to Power BI Desktop, we are aware we need to carry out a Transpose. So, rows become columns and column become rows. However, a careful look at the table (below) shows that we have already lost Row 1 because Power Query has turned it into a header. We must delete the Promoted Header Step and Anything after it.
Tip Number 1: When you need to use Transpose, you should delete any Promoted Header Step. (I will add some other tips to this in part 2 of this article).
After deleting the Promoted Header step (and other steps after it), we can now go to the Transform Tab and hit “Transpose”.
Oops….Now, Ship Mode and Segment are looking good but Order ID is looking bad. We must now find a way to make Order ID good while keeping Ship Mode and Segment intact. Surely, we cannot do a Transpose again because this will keep us running in circles. Transpose just flips the whole table. Instead, we’ll need something that can do a partial Transpose. Keep the good columns and Transpose only the bad one.
In order to have only the Order ID section to transpose, we must use Unpivot.Unpivot should make the Order IDs stand straight inside a column, while all the values form another column that matches the combination of each Segment, Ship Mode and Order ID. To achieve this, first we need to (a) Promote Headers (b) Fill down the nulls on Segment to carry the values above them (c) Select the good columns and ask Power Query to Unpivot the other columns (Order IDs)
Now we have the structure that we require. We will only have to rename the headings to our desire.
Conclusions and Things to Note:
It is important to think about the behavior of Transpose and Unpivot to know how it will affect our data tables.
When we use Transpose, our table will flip all rows to columns and vice versa.
Therefore, we must ensure that the table does not have headers, by deleting the promoted headers step from the Query Settings pane. If we don’t delete headers, we would have lost a row of data because Transpose makes rows become columns, the headings just tend to disappear.
When we use Unpivot, a section of the table is transposed. This is done by making all the headings of that section appear on a single column to be named Attribute by default, and everything under the headings now becomes a separate column to be named Value by default.
Therefore, to flip a particular section only, the items required to be on a column must first be on the headers row. We must have a Promoted Header step before Unpivoting.