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. = 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 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 Dynamic Data Types in Power Query Expand All Columns Dynamically in Power Query Create Dynamic File Path in Power Query   .        Automate repetitive data cleaning tasks using Power Query A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI   DOWNLOAD THE COURSE OUTLINE    |    ENROLL IN THE COURSE