Power Query: Name or Number

Welcome to our Power Query blog. Today, I look at an example where renaming a column is performed by column position.

I have some expense data that Mary, my reliable fictional salesperson has supplied:

I want to rename expense to Expense Type. I can double click the column or right-click and select ‘Rename’:

I have created a new step with the following M code

I make a few other changes and save my query.

I have similar data from Paul:

I use the Expenses query for this data too.

However, the results are not ideal:

Paul has called the column holding the expense type data Type, so column name expense is not recognised.  I need a way of renaming my column without using the original name.  I will use the formula Table.ColumnNames() instead of the original name of the column.

This function returns the names of columns from a table.

Table.ColumnNames will return all the column names from my table in a list, so Table.ColumnNames(Source) will give me all the column names in my source.  In order to select a particular column name, I need to specify where my column appears in the list, starting at 0 for the first column.  The expense type data is in the third column, so its index will be two (2).  Therefore, instead of my original step,

I will use

My column has been renamed without referencing the original column name. This is great so I save my query…

Another problem!  This time, Paul has not used a capital letter on the Amount column.  I can use a similar approach for the Expense Type column.  Instead of the step

I will use

This time, I need to direct my change at the fourth column, identified by index 3:

I have now created a more robust query that can cope with my salespeople’s column names.

Come back next time for more ways to use Power Query!