Power Query: Unpivot using Column Numbers rather than Names

by Jeff Robson

Say my name, say my name

When you’re working with data from Excel in Power Query, column names can often change, new columns can be added and columns can be deleted.

Excel users don’t think twice about doing any of these things as they all seem quite normal and logical however they can all cause problems for Power Query since, by default, it hardcoded column names into its M code.

If you’re doing an Unpivot Other Columns, you might want to choose the first two columns, then always unpivot all the rest.

But how do you make your code robust if the first two column names are known to change?

Am I just a Number to You?

In Power Query, yes! I’d prefer it if you were!

One way you can do this is to use a list of column names, then reference the column names using the column number, rather than the column name.

Steps:

Use the user interface to record your Unpivot as normal

If your system is setup to add an automatic Change Type, delete this step as it will also cause problems.

 

Go into the Advanced Editor and insert a line of code to place all the column names into a List

 

Edit your Unpivot step and replace the column names with List elements

Your unpivot will now always unpivot the first two columns regardless of their names.