Power Query provides a feature that is Unpivot columns which allows you to transform columns into attribute-value pairs, where columns become rows.
Columns that you unpivot are split in two columns that are Attribute and Value.
- Attribute represents the header of the columns chosen for Unpivot Columns.
- Value represents the value which was present under the header’s column previously.
In general the Unpivot Columns feature turns multiple column headers into a single column but in rows. The values get stored under the original columns in another column as shown in below screenshot.
There are three ways using that you can unpivot columns from a table as follows:
- Unpivot columns
- Unpivot other columns
- Unpivot only selected columns
As you can see, here we have a sample dataset named Item, and you can see Item rows and Year columns create a matrix of values.
You can copy a sample data for practice:
| Item | 2016 | 2017 | 2018 |
| Item-A | 5000 | 5000 | 2573 |
| Item-B | 4000 | 8954 | 8484 |
| Item-C | 5795 | 3578 | 2362 |
| Item-D | 3848 | 3500 | 2628 |
| Item-F | 3000 | 3467 | 3684 |
Now our aim is to transform the matrix into a table with unpivoted columns, so the idea is that transform a matrix as that you will have a set of Years in the table that should all be part of a single column and respective value for each Year and Item should be in a different column, that can be called as attribute-value pair as shown below.
Lets see how to perform unpivot columns operation in Power BI using Power Query.
Now go to Power Query Editor, for this Just click on Transform data tab.
Now you will see, a Power Query window opens.
Lets perform unpivot columns operation, and see the working of Unpivot columns, Unpivot other columns, Unpivot only selected columns.
Unpivot columns
Lets unpivot the columns using Unpivot columns operation.
There are two way that you can use to find Unpivot columns in Power Query Editor.
First way is that in data preview screen you can select column, or multiple columns( with Ctrl keyword) that you want to unpivot then right-click the columns that you want to unpivot, and select Unpivot Columns from context menu.
[Also read: Pivot columns – turn rows into columns]
The second way is that you can go Transform tab in the ribbon bar, then select the columns that you want unpivot and then select Unpivot Columns from drop down as shown below.
Once you click on Unpivot columns, you can see unpivot columns transforms the columns into attribute-value pairs, where columns become rows.
Also you can see on the right side of screen, Power Query creates a step for this in Query settings under Applied steps section.
Important Note, if you unpivot columns using this option and when you refresh your query and any new column is added into table then you will notice that the operation will be done on the updated column, but it will not affect the column that was not originally selected (in this case column Item), means any new column that is added to the source table will be unpivoted as well.
Unpivot other columns
Unpivot other columns operation allows you the ability where you can select the columns that you do not want to unpivot and unpivot the rest of the columns in the table.
Before moving further on this, you need a original dataset so just remove the unpivoted column step from Query settings that you have performed previously as shown below.
Once you remove the step, you will see the original dataset in data preview screen.
Now right click on Item column header and then select the Unpivot Other Columns from context menu.
Once you done with this, you will see it transforms the data exactly same as Unpivot column operation does.
If you like you can give meaningful name to Attribute column also, lets rename the Attribute column to Year. Just double click on Attribute column header and change it to year.
Important Note, this transformation is similar to the Unpivot columns operation, when your query is refreshed and new column is added into table then all the columns will be unpivoted except the ones that were previously selected.
This transformation can be crucial for queries that have an unknown number of columns. The operation will unpivot all columns from your table except the ones that you have selected.
Unpivot only selected columns
Unpivot only selected columns operation allows you to unpivot only specific columns from your table. You can use this option for scenarios where you are dealing with an unknown number of columns from your data source and you only want to unpivot the selected columns.
Again, before moving further you need a original dataset, so lets remove the last query transform steps from Query settings that you have done for Unpivot other columns previously.
Once you done with that, you will see a original dataset as shown below.
Lets unpivot all the column except Item column, from data preview just select all year columns (select multiple columns with CTRL keyword), then right-click any of the columns you selected, and then select Unpivot only selected columns from context menu.
Once you done this, you will see the output exactly same as you got with other two unpivot options.
Important note, When you refresh query, and a new Year column– 2019 and a new rows for Item-G is added into table then the output of the query will be different from the previous examples. Â
So this transformation will not change the header for Year column 2019, and it remains unchanged while remaining Year columns are unpivoted as the unpivot operation was applied only on the Year columns – 2016, 2017, and 2018.
Lets demonstrate the case as mentioned in above note, assume that a new row for Item-G and a new column for Year – 2019 is added into table, as shown below.
And when you refresh the query, then this transformation will transform the output as shown below.
You can see, newly column Year-2019 remains unchanged while other columns for Year -2016,2017, and 2018 are unpivoted as the unpivot operation was applied on these columns.
Also Read..
Group By the data using Power Query
Create Index column using Power Query
Import text using examples feature Power BI
Pivot columns – turn rows into columns
Choose and Remove Columns from table
Data Profiling: Column Quality, Column Distribution & Column Profile
Fill values operation: fill down and fill up
