Unpivoting other columns is a pretty useful feature in Power Query but it has some quirks of it own. In this post, I’ll share how can you make Unpivot Other Columns Dynamic.
Dynamic Unpivoting in Power Query – Video
Consider this Data
The given table has Product, Color and Month columns from January through December. I wish to unpivot all the month columns.
Unpivoting this data
If I select the 2 columns (Color & Product) and unpivot other columns, Power Query is going to hard code the names of the columns which not Unpivoted.
That means any new column added to the data (which is not supposed to be unpivoted) would get unpivoted automatically. Therefore, we need to find a way make the hardcoded columns dynamic.
Dynamic Unpivoting in Power Query
The logic is to create some sort of pattern to select the columns that will not be unpivoted.
Step 1 – Extract all Column Names – I’ll add a new step with the following m code to extract all column names.
Step 2 – Convert this list into a table – Right click on list header and select To Table option.
Step 3 – Add a new column to convert the values to dates. I am trying to create a pattern to select all columns which can’t be converted to dates. Create a new custom column with the following code.
= Date.From('1' & [Column1] & '2020')
The Product, Color and Brand columns will obviously display an error.
Step 4 – Keep the Errors (Home Tab >> Keep Rows>> Keep Error Rows) – Keeping these errors will dynamically result in a list of columns that should not be unpivoted.
Step 5 – Converting the table back to a list – Right Click on Column 1 >> Drill Down
Step 6 – Renaming this step – for easy identification, I rename this step to ColumnNames
Step 7 – Unpivoting Again
- Create a new step which refers to the Source
- Then select the 2 columns (Product and Color) > Right click > Unpivot Other Columns
Step 8 – Making Unpivoting Dynamic – As you’d expect the names are still hard-coded but I’ll replace them with the “ColumnNames”
Now if you add more columns other than months which are not supposed to be unpivoted, Power Query is smart enough to figure that out.
More on Power Query