Understanding how Pivot and Unpivot work in Power Query can be super useful. Pivot will convert rows to columns while Unpivot converts columns to rows. We recognize that it’s not exactly intuitive but we’ll provide examples of what each one does to help make it easier to understand.
Pivot and Unpivot are two very common Power Query functions. They’re especially useful in accounting and finance use cases where months or dates often go along the top of a page as headers which is nice for presentation but annoying for analysis or vice-versa. Pivot and Unpivot are similar functions that act as opposites. We’ll explain.
Let’s dive in!
Table of Contents
Power Query Pivot vs Unpivot
Pivot and Unpivot are two techniques to transpose data in Power Query. Pivot transforms rows into columns and Unpivot converts columns into rows. Use Pivot to go from Months as column headers to rows of a single column and Unpivot to convert rows into individual columns.
Pivoting is the process of transforming data from a long format to a wide format. It turns unique values from one column into multiple columns in the output, summarizing data in the process.
Unpivoting is the reverse process of pivoting. It transforms data from a wide format to a long format, turning multiple columns into rows, making the dataset more normalized or “tall”.
Power Query Pivot Rows to Columns
To convert rows to Columns in Power Query, select the column you want to use as column headers. Then select “Pivot Column” from the “Transform” section of the Power Query Ribbon. In the dialog box, select the column that contains the values that will fill out the resulting table.
Here’s an example of a data set with 3 columns. Column1 represents the name of financial statement rows. The Attribute Column contains the abbreviate for month names that we want to show as column headers. The final column is called Value which will fill out our grid of data.
Select the column that you want to become the headers of a newly transformed table.
Press the Pivot Column button in Power Query from the Transform section of the Ribbon.
In the Pivot Column dialog box, select the name of the column that will become the values of the newly filled out grid of data.
The resulting table has the rows from a single column now across the top of the page as headers.
In the next example, we’ll use the exact same dataset to unpivot the columns. Pivot and Unpivot act in the opposite way of each other. Pivot moves rows to columns, and Unpivot moves columns to rows.
Power Query Unpivot Columns to Rows
To convert Columns to Rows in Power Query, select the column you want to use as rows. Then select “Unpivot Other Columns” from the “Transform” section of the Power Query Ribbon. This will create two new columns called Attribute (Prior Column Headers) and Values.
Here is an example using months across the top of a dataset as column headers, and financial statement line items for rows.
With the column of financial statement line items selected, click on “Unpivot Columns” from the “Transform” menu and select “Unpivot Other Columns”.
Alternatively, you could also select all other columns than the first column, and select Unpivot Columns.
The results are two new columns, one called Attribute which represents the prior column headers, and the values are placed into a single column. The names Attribute and Value are standard and automatically generated by Power Query.
This technique can be useful when you need to apply additional transformations to column headers, for example unpivot the data, filter out specific column names, then pivot to put it back into the original order after the dynamic filtering is applied.
Power Query Pivot Without Aggregation
A Power Query Pivot will automatically aggregate data when multiple combinations of the same row and column are detected. You can adjust the aggregation method under Advanced Settings of the Pivot Dialog box, which includes a “Don’t Aggregate” option.
Power Query Pivot aggregation methods include Count, Count Non Blanks, Min, Max, Median, Average, Sum, and Don’t Aggregate.
Uncommon Power Query Pivot and Unpivot Use Cases
Pivoting and unpivoting in Power Query are typically associated with reshaping data for standard reporting formats. However, these transformations can be combined and used creative ways to solve unique data challenges. Here are some uncommon use cases where pivot and unpivot functionalities can be particularly useful.
Let’s delve into these specific and advanced use cases of Power Query transformations, illustrating how they can be utilized to address complex data preparation challenges:
Using an Index Column to Filter, Transform, and Re-combine
This technique involves adding an index column to your dataset before performing operations like filtering or transforming specific rows. The index column serves as a unique identifier for each row, allowing you to split your data, apply different operations to subsets, and then recombine the rows into the same order that they started..
Pivot and Unpivot Together to Dynamically Filter out Blank Columns or Specific Headers
Pivot and Unpivot can be used in tandem to clean up a dataset by dynamically removing blank columns or filtering out specific headers. Start by using Unpivot to place column headers into a single column. Then apply filters to the attribute column and Pivot to place the remaining rows back to column headers.
Combining Files with Different Headers
When dealing with multiple data files that have similar data but use different column headers, you can utilize a helper query to Unpivot the data first which puts all column headers and values into two columns of the same name. It’s not strictly necessary but it can be easier to work with column names in this way to rename them prior to applying a Pivot to have Power Query automatically align data.
Pivot and Unpivot are two extremely useful features of Power Query. However, it can be confusing remembering which one to use. Unpivot will convert data with many different columns into two columns, one for the former column headers and another for the values. Pivot will convert rows of data into column headers and aggregate values based on a defined method.
Power Query Pivot and Unpivot are not mutually exclusive, meaning that there are a number of use cases where you can and should use the two together. These tools allow you to dynamically filter and rename column headers that would otherwise be a lot of work and otherwise wouldn’t be dynamic.