Promoting Headers Issue in Power Query

Be careful if a first row name changes

Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.

The button that creates the Headers is shown below.

The step that is inserted is called Promoted Headers.

Nowadays when you import data that has obvious headers Power Query will Promote the header row automatically, as you will see from the example that follows.

Problem

You shouldn’t promote headers if the name in the header row could change. The reason is that once you have promoted the headers the column names are used to refer to the columns in future steps. Those future steps will be invalid if the column name changes.

Example

In the CSV file below the column labelled APR changes based on the month the report is run for.

When we import the CSV via Power Query it automatically promotes the headers.

It also changed the type of the APR column to 123.

This Changed Type step will cause the Query to error out on this step if the column is not labelled APR.

Below is an example of another CSV being uploaded this time it has MAY as the column heading. Note the error displayed.

If we Refresh the Query an error is displayed on the Change Type step.

If we examine the formula bar we see why – it mentions the APR column name which is now called MAY.

Solution

It is much safer to rename the columns yourself – it may take more time, but it won’t cause errors in the future. Double click the column name to change it. You can then delete the first row as it isn’t needed.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.