Power Query: Rename columns in nested tables from list (without errors)

Power Query is a nightmare when we want to combine data with different column names, and even worse when those columns are not in the same order. Can we even combine that data? You bet we can! We’re looking at that today; using Power Query to rename columns in nested tables based on a list, without creating errors.

In previous posts, I’ve used the code-stealing method, but not today; we will write the M code ourselves 😲. Don’t worry; it will all work out OK.

Table of Contents

• Example
• Solution
• Promote Headers
• Rename columns
• Rename columns from a table
• Combine data
• Conclusion

Watch the video

Watch the video on YouTube

Example

In the example file, there are two queries: ExampleWorkbook and FindReplace.

ExcelWorkbook

In the ExcelWorkbook query, we connected to an Excel workbook with 3 worksheets (Sheet1, Sheet2, and Sheet3).

Those worksheets have different column names and are in different orders.

• Sheet1 – Column names: Item, Region, Value
• Sheet2 – Column names: Product, Region, Value
• Sheet3 – Column names: Item, Value, Division

Sheet column names are highlighted in the screenshots below.

If the columns were in the same order, we could combine them using the default Column1, Column2, and Column3 names. Then we would just rename the columns afterward.

But our columns are not in the same order; therefore, this won’t work. So, we need to pull some Power Query magic to achieve this.

FindReplace

The FindReplace query contains a table detailing the column names we wish to rename. We will use this later in the post.

Solution

OK, now let’s tackle this problem.

Promote Headers

Because the example data is an Excel Worksheet, the headers are been promoted automatically. The headers are currently Column1, Column2, and Column3.

To promote the headers of a nested table, click on the fx icon in the formula bar and end the following formula:

= Table.TransformColumns(Source, {{"Data", each Table.PromoteHeaders(_), type table}})

Let’s break this down a little bit:

• Table.TransformColumns: The function for performing transformations on columns
• Source: Name of the Table to perform the transformation on (i.e., the previous step)
• “Data”: The column to perform the transformation on
• each Table.PromoteHeaders(_): The transformation to perform. The keyword each and the underscore ( _ ) are used because we have a nested Table.
• type table: The output of the transformation is a table data type.

Now, each of our Tables looks like this (headers promoted, but still different).

Rename columns

OK, so now we can rename the column. Click the fx icon; in the formula bar, add the following function:

= Table.TransformColumns(Custom1, {{"Data", each Table.RenameColumns(_,{{"Product","Item"}}), type table}})

Let’s break this down also:

• Table.TransformColumns: The function for performing transformations on columns
• Custom1: Name of the Table to perform the transformation on (i.e., the previous step)
• “Data”: The column to perform the transformation on
• each Table.RenameColumns(_: The transformation to perform. The keyword each and the underscore ( _ ) are used because we have a nested Table.
• {{“Product”,”Item”}}: The rename we wish to perform (e.g., rename Product to Item)
• type table: The output of the transformation is a table data type.

Now look at the Preview Window in Power Query. Two of the Tables show errors.

This occurs because only one table has a column called Product. The other tables do not have a Product column, so it creates an error.

Let’s turn to the magic MissingField.Ignore argument. This is an optional argument of the Table.RenameColumns function. You’ll only really know it’s there if you look in the Power Query documentation, or write M code formulas yourself.

Change the transformation as follows:

= Table.TransformColumns(Custom1, {{"Data", each Table.RenameColumns(_,{{"Product","Item"}}, MissingField.Ignore), type table}})

• MissingField.Ignore: Setting to ignore any fields which are missing, therefore avoiding an error

Find out about the MissingField options here: https://learn.microsoft.com/en-us/powerquery-m/missingfield-type

Now, the Tables do not show errors.

Now let’s add all the columns to rename step:

= Table.TransformColumns(Custom1, {{"Data", each Table.RenameColumns(_,{{"Product","Item"},{"Division","Region"}}, MissingField.Ignore), type table}})

• {{“Product”,”Item”},{“Division”,”Region”}}: This is a list of lists. Product is renamed to Item, and Division is renamed to Region. We can keep adding pairs of names in the same syntax.

Now the nested tables all have the same column headers.

Rename columns from a table

It might become a little cumbersome to keep adding pairs of column names. Instead, we can use a separate table. This is what the FindReplace query is for.

To rename columns based on a table, we replace the list of renames with the List.Zip function containing the Table and column names (see example below).

= Table.TransformColumns(Custom1, {{"Data", each Table.RenameColumns(_,List.Zip({FindReplace[Find],FindReplace[Replace]}), MissingField.Ignore), type table}})

Combine data

Because all the tables have the same column names, we can remove unnecessary columns, then click the Expand icon and combine the data.

MAGIC!!!

Conclusion

Dealing with nested tables containing different column names can be tricky in Power Query. But with the techniques in this post, you should be able to obtain consistent column names across all nested tables.

Related Posts:

• How to expand columns dynamically in Power Query
• Rename columns in Power Query when names change
• Power Query: How to Combine files based on a list

---

What next?

Discover how you can automate your work with our Excel courses and tools.