Is there anything that Power Query can't do?

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.

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:

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:

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:

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:

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.