Dynamically renaming columns in Power Query

The fact that a rename columns step in Power Query is hard-coded, has resulted in some challenges for many people. Of course, when we import data, the names of the columns and the structure should always remain the same in the source. If this isn’t the case, our steps in which the columns names are hard-coded, will result in errors and this is an expected result. However, if we look at this blogpost in which we imported files from a folder, we still have this challenge even though the structure of our source files do not change.

Let’s pick up where we left last time. After importing the files, our data looks like this:

Now we have to promote the first row as headers in order to get the correct column names:

Now all columns have a correct column name, but the first column does not. Because the filename is linked to all of the rows in the three files, the filename will be promoted as a header. In this case the column name is not correct as we want to call this column “ConcernedFile”. Let’s rename the column:

= Table.RenameColumns(#"Promoted Headers",{{"1. SalesDataJan2023.xlsx", "ConcernedFile"}})

As you can see in the code above, the column name that we want to rename (“1. SalesDataJan2023.xlsx”) is hard-coded. This means that if in a future situation, when the files in the folder are sorted in a different way, or when new files are added, the first file could be a different one. What then happens is that Power Query tries to rename the column based on the hard-coded column name, even though the column now has a different name e.g. “0. SalesDatJan2023Correction.xlsx”. This will result in an error and the query will break:

In this case, we do not want to rename the first column based on its name but based on its position. This because we know that the first column will always be the filename one. In this example we have 5 columns and we always want to rename the first column to “ConcernedFile”. Before we proceed with customizing our M code to accomplish this, we first have to understand what the Renamed Columns step is doing syntax-wise:

= Table.RenameColumns(#"Promoted Headers",{{"0. SalesDatJan2023Correction.xlsx", "ConcernedFile"}, {"SalesID", "SalesTest"}})

In the code above, we are renaming the 0. SalesDatJan2023Correction.xlsx column to ConcernedFile and we rename SalesID to SalesTest.

The first part before the comma is always a reference to the previous step in the query, in this case #”Promoted Headers”. In the next part, Power Query requires a list of column pairs that we want to rename. In the following column pair that is wrapped in a list, we specify that we want to rename “0. SalesDatJan2023Correction.xlsx” to “ConcernedFile”:

{"0. SalesDatJan2023Correction.xlsx", "ConcernedYear"}

The fact that those columns are wrapped in {}, makes it a list. Power Query requires all of those column pairs in separate lists that is wrapped in another list:

{{"0. SalesDatJan2023Correction.xlsx", "ConcernedYear"}, {"SalesID", "SalesTest"}}

Now that we have a better understanding of the syntax of the renamed columns step, we can start modifying the code to make it dynamic. For this, we also need the Table.ColumnNames function. This function simply creates a list of all columns in the table. The only parameter is the name of the previous step, so if we apply this function in our query:

Table.ColumnNames(#"Promoted Headers")

We will get the following list of column names:

The first column is the one that we want to rename to ConcernedFile. Based on the list above, we can retrieve single values by specifying which column we want to see based on its order (index) in the list. Power Query starts counting from 0, so number 0 is the column we want to rename. In order to retrieve single values from a list, we can use the positional index operator, which requires the number to again be wrapped in curly brackets {}. The following code will retrieve the first (0st) value from the list above:

= Table.ColumnNames(#"Promoted Headers"){0}

It will result in a singe value:

The single value that we have retrieved with the code above, can be used in our Renamed Columns step. Our initial code was:

= Table.RenameColumns(#"Promoted Headers",{{"0. SalesDatJan2023Correction.xlsx", "ConcernedFile"}, {"SalesID", "SalesTest"}})

We want to make the “0. SalesDatJan2023Correction.xlsx” part dynamic, so we adjust the code to:

= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "ConcernedFile"}, {"SalesID", "SalesTest"}})

And that’s it. Now every time the query is refreshed, the first column will dynamically be renamed. The other columns, which are hard-coded, are fine because we know that the structure of our sources should not and will not change.