How to Combine Headers in Excel Tables Using Power Query and Custom Functions

Combining headers in excel tables using power query in excel formula is a technique that allows you to merge two or more rows of headers into one row, using a delimiter of your choice. This can be useful when you have data that has multiple levels of headers, such as categories and subcategories, or units and measures.

The basic theory behind this technique is to use a custom function that takes the original table, the number of header rows, and the optional delimiter as parameters, and returns a new table with the combined headers. The custom function works by:

• Splitting the original table into two parts: the header rows and the data rows.
• Converting the header rows to a list of lists, where each list contains the values of one column.
• Zipping the lists together, so that each list contains the values of one header row.
• Combining the lists into a single value, using the delimiter to separate the values.
• Converting the list of values back to a single column table, and transposing it to a single row.
• Appending the data rows to the new table, and promoting the first row to the header.

The procedures to apply this technique are as follows:

• Create a custom function in Power Query, using the code provided in this article or this article. You can name the function as you like, but for this example, we will call it CombineHeaders.
• Format your data as an Excel table, and make sure that the headers are in the first rows of the table. You can have any number of header rows, but they must have the same number of columns as the data rows.
• Load your table into Power Query, using the Data tab and the From Table/Range command. Make sure to uncheck the My table has headers option, as we want to keep the headers as part of the data.
• Invoke the custom function on your table, using the Add Column tab and the Invoke Custom Function command. You need to specify the name of the function, the original table, the number of header rows, and the optional delimiter. For example, if your table is named Table1, and you have two header rows, and you want to use an underscore as the delimiter, you can use the following formula: =CombineHeaders([Table1],2,"_")
• Expand the new column that contains the result of the function, by clicking on the expand icon and selecting Expand to New Rows.
• Close and load the query to a worksheet or a data model, as you prefer.

To illustrate this technique with a detailed example, let’s use the following table as our data source:

We want to combine the two header rows into one, using an underscore as the delimiter, so that the result looks like this:

To do this, we follow these steps:

• Format the data as an Excel table, and name it Table1.
• Load the table into Power Query, and uncheck the My table has headers option.
• Create a custom function in Power Query, using the code from this article or this article, and name it CombineHeaders.
• Invoke the custom function on the table, using the formula =CombineHeaders([Table1],2,"_").
• Expand the new column to new rows, and promote the first row to the header.
• Close and load the query to a worksheet or a data model.

The result is a new table with the combined headers, as shown below:

Another approach to combine headers in excel tables using power query is to use the Table.PromoteHeaders function, which can promote multiple rows to headers, and concatenate the values with a dot as the delimiter. However, this function does not allow you to specify a different delimiter, and it may cause errors if the headers contain null or duplicate values. You can read more about this function here.