Dealing With Tables With Changing Headers in Power Query

One of the more common errors you’ll run into when using Power Query is when your headers change names. If that happens, and you have a step in Power Query that alters the headers or relies on them in any way, you’ll end up with an error saying a header wasn’t found. This can happen if you are querying a table whose headers change or if you simply change the name of one of them yourself. Below, I’ll show you an effective strategy for dealing with tables with changing headers in Power Query so that even if the header names change, you can avoid running into these errors.

Changing headers in Power Query

Let’s start with the basics of how you would normally create headers in Power Query. For this example, I’m going to use data from the City of New York Expenses, which you can download from here. This is what the data set looks like:

To launch this data into Power Query, go to the Data tab and click on From Sheet. Once the range is selected, click on OK. Once in Power Query, the data looks as follows:

To change any header name in Power Query, all you need to do is double-click on the header. In this example, I’m going to change the first header so that rather than Publication Date, it will just say Date. Then, I’ll go in and click Close & Load to get out of Power Query.

The problem arises if the header in the table were to change. If I go back into the Expense_Actuals sheet (which Excel automatically created for me when I set up the table in Power Query), it shows these headers:

And if I change the first header so that it looks like this:

I’m going to have a problem, because Power Query is going to be looking for Publication Date rather than just Date. Now, if I go under the Data tab and click Refresh All — which will update the query — I get the following error:

This is the error that shows up when Power Query can’t find the header. If I modified the header name for Fiscal Year or any other column, then I wouldn’t get this error. The reason is the step of changing Publication Date to Date is hardcoded into Power Query and if it doesn’t find that header, it will give me the above error message.

The key to dealing with tables with changing headers in power query is to demote them, which I’ll cover next.

Promoting and demoting headers in Power Query

By default, Power Query will use the first row of your table as its headers. In my situation, this is a problem because if the header in the table is changing, I could run into errors. To eliminate this, I’m going to demote the headers. To start, I’ll go back to edit the query. To do this, you can go under the Queries & Connections table, right-click on the query and click on Edit:

Then, select the first Power Query step (which will likely just be ‘Source’), and you should again see the table again:

Then, under the Transform tab, in the Table section, click on the option to Use Headers as First Row:

This has the effect of demoting the headers so that they are now rows. And upon doing so, the header names become just Column1, Column2, etc:

The option above it to Use First Row as Headers is the exact opposite — it would promote the first row and make that the header for all the columns; it would undo the above step.

However, now with the plain column names, what you can do is go through and re-name each of the headers however you want, which now looks as follows:

The one issue that remains here is that now we have the old header names in the first row. To get rid of them, go into the Home tab and click on Remove Rows and select the option to Remove Top Rows

And then for the number of rows to remove, just enter 1 and click OK:

And then, the updated Power Query table looks as follows:

You can remove any other steps that were previously in Power Query to avoid it looking for the old header names.

One thing to also remember is the Changed Type step which Power Query automatically generates and looks to convert each header into the correct data type. As you can see from the formula for that step, it will look for the hardcoded header names:

You can either modify the header names within the formula, or you can just remove the step entirely. But this too can cause issues if it is looking for a hardcoded header name. Once you’ve made the last step of removing or changing the Changed Type step, you should be good to go.

Now, when you go and refresh your query, even if you have modified the header names, your data will refresh correctly and put in the header names you have specified in Power Query.

If you liked this post on dealing with tables with changing headers in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.