How to Merge Excel Files with Different Headers in Power Query

Appending the data in several Excel files can be complicated if the column headers all have different names. When you try to merge these files, some columns may disappear, and some cells might list “null” instead of your data. Follow these steps to properly merge these files after you have combined them in Power Query.

1. Delete the “Changed Type” Step

At the end of the transactions query in the Power Query dialog, delete the “Changed Type” step in the “Applied Steps” list. This step sets the text of the old column header, so deleting the “Changed Type” line will prevent errors.

2. Rename the Header

Select the query you need by clicking “Transform Sample File from Transactions” on the left-hand side. Rename the column you want to change by right-clicking on that column and selecting “Rename.” Then, type in the new name.

3. Wrap the Formula in a Try/Otherwise Clause

Add a try/otherwise clause to your formula to adjust the column name. If it works, the formula will return a result. If it does not work, it will return an alternate name. This formula is similar to the IFERROR formula in Excel.

You can set up this formula to change the column name or choose a name from the previous step in the query. In the formula bar, change the syntax to:

= try Table.RenameColumns(#”Promoted Headers”,{{“old header name”, “new header name”}}) otherwise #”Promoted Headers”

Where this formula says “old header name,” add the header name you want to change in between the quotation marks. For “new header name,” add the new header name for your appended table. The terms are case-sensitive, and the syntax must be exact for the formula to work.

When you return to the “Transactions” query, the files should be successfully merged with new header names.

Learn More About Our Managed IT Services

Agio offers managed IT services for financial service companies to make your IT frictionless for your team. We understand your IT issues and resolve them with proactive monitoring, 24/7 user support, consistent uptime, transparency into cases, scalability, and more. As a result, your organization will have a reliable network and increased productivity — all for a lower cost.

Connect with an Agio team member by contacting us online today.