Partners,,,, russianxnxx

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.

Dynamic Unpivoting in Power Query-Data


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.

Dynamic Unpivoting in Power Query- Hard coded

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)

Dynamic Unpivoting in Power Query- Headers


Step 2 – Convert this list into a table – Right click on list header and select To Table option.

Dynamic Unpivoting in Power Query-To Table


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.

Dynamic Unpivoting in Power Query-Custom Column


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.

Dynamic Unpivoting in Power Query- Keep Errors


Step 5 – Converting the table back to a list – Right Click on Column 1 >> Drill Down

Dynamic Unpivoting in Power Query- Drill Down


Step 6 – Renaming this step – for easy identification, I rename this step to ColumnNames

Dynamic Unpivoting in Power Query- Rename


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”

Dynamic Unpivoting in Power Query-Output


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.


    ⬇️ Pop in your Name & Email to get the file!


    More on Power Query

    Dynamic Data Types in Power Query

    Expand All Columns Dynamically in Power Query

    Create Dynamic File Path in Power Query


    Topics that I write about...