Dynamic Unpivoting in Power Query

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.

= Table.ColumnNames(Source)

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

1. Create a new step which refers to the Source
2. 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

Dynamic Data Types in Power Query

Expand All Columns Dynamically in Power Query

Create Dynamic File Path in Power Query