Merge Files With Different Column Headers

A client contacted me today asking how to merge files with different column headers in Power Query.  The issue she's facing is that some of the files in her folder have a column called "customer", where others have a column called "ship to/customer".  Plainly there has been a specification change somewhere down the line, but it's causing issues in the combination - an issue that would affect either Excel or Power BI.

What happens when we try to merge files with different column headers?

In order to replicate this issue, I created two very simple CSV files as shown here:

image

I dropped these into a folder called "Test" and then

  • Created a new query From File --> From Folder
  • Renamed the query to FilesList (making a query that I can use to easily sort/filter the list of files later)
  • Right clicked the query in the Queries pane --> Reference
  • Renamed this query to Transactions
  • Clicked the Combine Binaries button

At this point I was presented with the following window:

image

The only thing I really want to point out here is that I choose the Example file which has the column name that I do NOT want.  (I want to rename "ship to/customer", so it's important that it show up here.)

I then clicked OK, and was presented with this:

image

Err.. wait… what happened to my customer column?

Why is the Customer column missing?

To understand this, we need to look at the steps in the Transaction query:

image

If you were to click on the "Invoked Custom Function1" step, you'd see that it adds a new column to the Transaction query.  The first table shows 3 columns where the first column is "ship to/customer".  The second table also shows 3 columns, but in this the first is "customer".  So all is working so far.

But then, when you get to the the "Expanded Table Column1" step of the Transactions query, it expands to show only the "ship to/customer" column.  Why?  It's because of the following M code generated by Power Query:

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Transactions", Table.ColumnNames(#"Transform File from Transactions"(#"Sample File")))

What this means in English is that it reads the columns from the table in the first sample file. That's not super helpful.

Now we could work on trying to enumerate all headers, but that would be a pain, as the code is complicated and still leaves us in a place where we would need to combine both columns anyway.  Let's fix this by dealing with it at the source.

How to merge files with different column headers properly

Step 1: Prepare the Transactions query:

Delete the Changed Type step at the end of Transactions query.  This is because it is setting the "ship to/customer" column to text, and by the time we're done, that column will be called "customer".  If we leave the step as is, it will cause an error.

Step 2: Modify the Transform Sample query:

Next we need to select the Transform Sample query:

image

Now, what we want to do is rename that "ship to/customer" column to make it "customer".  So let's do that:

  • Right click "ship to/customer" --> Rename --> "customer"

The problem here though, is that when we apply this to our other files, THIS will cause an error. Why?  They don't have a "ship to/customer" column to rename.  So we need to wrap this in an error handler.

To do this, we need to adjust the formula that was just created to wrap it in a "try/otherwise" clause.  This is essentially equivalent to Excel's IFERROR() formula.  If it works, it will return the result.  If not, it returns an alternate item, which we will set to be the previous step in the query.  In other words "Try to rename this column.  But if it fails, give me the original table.

The keys here are to

  • Insert the try and otherwise in the correct location (remember they are case sensitive)
  • Get the right syntax for the previous step name (remember to wrap it in #" " if the step name has a space in it.)

In this case, it should look like this:

image

Step 3: Revel in your success:

You got it.  At this point, returning to your Transactions query should leave you pretty pleased, as we've plainly been able to successfully merge files with different column headers into the table that we actually want:

image

The only thing left to do is set the data types, and we're done. Smile

11 thoughts on “Merge Files With Different Column Headers

  1. Huh, never would have thought to return a table with "try...otherwise". Very slick technique.

  2. That's much more elegant than any solution I've used before, thanks Ken!

  3. @ Ken - I use the below approach
    1) demote headers
    2) Remove top 1 row
    3) All columns are now Column 1 , Column 2 etc
    4) Merge this to an Table that has only the headers (typically maintained locally or in a separate XL file.
    This only requires the Columns in the source file to be in the same order.
    Each file the headers could be slightly different

  4. Hey Sam,

    The benefit of your approach is that you don't care how often the column names change. The drawback is that the columns must be in the same order.

    The benefit of mine is that I don't care about the order. The drawback, of course, is that if the column names keep changing, then mine breaks.

    The right approach really comes down to trying to figure out how your data structure is likely to change.

  5. I had this EXACT scenario today. My solution, not as elegant was:
    1) Start with file of column header I did not want to keep
    2) Remove the automatic "promote headers" in the transform query
    3) create a new column. If first column = "name I don't want" then "name I do want" else first column. Now I have a new column that is what I want. Delete the first column, promote headers, done.

  6. That works too, Ed. I wanted to avoid this route mainly because I didn't feel that adding a new conditional column was necessary, but again also because in this scenario you must have all the columns in the same order. But again, it really depends on the way the shape of your data changes.

  7. I think there is an even easier solution to this problem.

    There is an optional argument in the Table.RenameColumns function:
    Table.RenameColumns(table as table, renames as list, optional missingField as nullable number)

    If you use "MissingField.Ignore" in the optional MissingField argument, it will ignore any renames where the column does not exist. This way you don't have to wrap the function in any sort of logical.

    Table.RenameColumns(#"Promoted Headers", {{"ship to/customer", "customer"}}, MissingField.Ignore)

  8. Maybe this formula will do the job too?

    Table.RenameColumns(#"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){0}, "customer"})

    I often get an error when changing data type right after Promoted Headers and this handy formula saves me all the time!

    Hey, btw I really wanted to attend your PQ training course but since I live in Japan, that's a long way to go...any chance of video session?

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

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