ICAEW.com works better with JavaScript enabled.

Is there anything that Power Query can't do?

Author: Simon Hurst

Published: 30 May 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

There are many things that Power Query can't do but, in this occasional series, we will be looking at some of the less obvious techniques available. In this episode we look at automating the changing of column heading text.


To follow the example of Douglas Adams, let's eliminate any possible anxiety by avoiding unnecessary jeopardy. The answer to the title question is: "Yes, loads of things". However, in this occasional series, we will be looking at some of the less likely solutions that Power Query can help with. We start in this episode by looking at how you can automate the naming of columns.

Rename columns

Of course, it is straightforward to rename a column in Power Query, you just double-click in the column heading and type in the new name. However, this requires you to specify the original name of each column that you need to change. When you rename a column, Power Query creates a Step that references the original column name and the new name:

= Table.RenameColumns(#"Changed Type",{{"Service charge 1", "Charge 1"}})

If the original column is not present, or the heading is changed in the underlying data table, the step will generate an error. This can make it difficult to automate the process if the columns in use might vary each time the query is run. In this rather simplified example, each type of service charge is entered in a separate column. Our aim is to end up with a table that just includes the columns that have values. We also want to change all the column names from Service Charges to just Charges, followed by the sequential number:

Screenshot of column name examples in Excel

Manually changing the column headings would work until we deleted a column or added a new column to our data table. We could achieve the same result more automatically in Power Query. Having loaded our original Table into Power Query we can right-click in the Client column heading and choose to Unpivot Other Columns. This creates a new column that contains our original column headings. Because our column headings are now in a column, we can apply any of the standard Power Query operations to them. For example, we could right-click on any of the values in our column and use Replace Values to change our Service charge to Charge:

Screenshot showing how to Replace Values in Excel

We can then sort the column using the column heading dropdown. Having changed our column headings, and sorted them, we can select our Attribute column and use the Transform Ribbon tab, Any Column, Pivot Column command to Pivot the Attribute column back to being the column headings for our query:

Screenshot showing renamed column headings in Excel

In this case we used the Unpivot command to transpose our headings into rows and remove the unused columns, but Power Query also has a Transpose command in the Transform Ribbon tab that will Transpose a whole table, swapping all columns for rows. To use this to convert our column headings into a useable column we would first need to use the Home Ribbon tab, Use First Row as Headers dropdown to choose 'Use Headers as First Row'. Transpose would then include our column headings as our first table column:

Screenshot showing how to Transpose a table in Excel

Having made our required changes to the column headings, we could then use Transpose again to turn our original rows back into rows and our columns back into columns, before using the 'Use First Row as Headers' command to reinstate our column headings.


By converting our column headings into a table column, we can manipulate the heading text without at any point needing to specify an individual column heading name in our Power Query steps. This means that our query should still work without manual intervention even if we were to delete columns or add new columns to our underlying data. We would however also need to delete the automatically generated Changed Type steps which also refer to each column by name.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.