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.
Returns a table that is the result of merging a list of tables, tables.The resulting table will have a row type structure defined by columns or by a union of the input types if columns is not specified. Apr 20, 2020 Returns a table that is the result of merging a list of tables, tables. The resulting table will have a row type structure defined by columns or by a union of the input types if columns is not specified. Example 1 Merge the three tables together. Mar 04, 2021 Use Power Query to Combine Tables You can use Power Query (Get & Transform Data) to combine the data from 2 or more tables, if those tables have some column headings with identical names. Then, create a pivot table from the combined data NOTE: In older versions of Excel, use the Multiple Consolidation feature. Mar 02, 2021 Clicking OK in the Merge dialog box opens the Power Query Editor, where you select the columns to be added from table 3. In this example, we add only the Commission column: As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables. Learn how to combine files in a folder when the data in the files has inconsistent column headings and table names. 👇Check out my full courses and ebooks he.
In order to replicate this issue, I created two very simple CSV files as shown here:
Power Query Merge Two Tables With Different Columns
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:
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:
Err.. wait… what happened to my customer column?
To understand this, we need to look at the steps in the Transaction query:
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.
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.
Power Query Combine Tables With Different Columns Word
Step 2: Modify the Transform Sample query:
Next we need to select the Transform Sample query:
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'
Power Query Combine Tables With Different Columns
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.
Power Query Combine Text Columns
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:
Step 3: Revel in your success:
Power Query Combine Tables With Different Columns Free
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:
Power Query Combine Tables With Different Columns In Python
The only thing left to do is set the data types, and we're done.