Table.PromoteHeaders

What is Table.PromoteHeaders?

Table.PromoteHeaders is a Power Query M function that allows you to promote the first row of a table to the header row. When a table is created in Power Query, the first row is typically assumed to be the header row. However, this may not always be the case. Table.PromoteHeaders allows you to specify which row should be promoted to the header row, which can be especially useful when dealing with tables that have multiple header rows.

How to Use Table.PromoteHeaders

To use Table.PromoteHeaders in Power Query, follow these steps:

1. Open the Power Query editor by selecting “Edit Queries” from the “Home” tab in Excel.

2. Select the table you want to modify.

3. From the “Transform” tab, select “Use First Row As Headers”.

4. If the first row is not the header row, select “Use Headers As First Row” instead.

5. If the table has multiple header rows, select “Use Headers As First Rows” and specify the number of rows to promote.

6. Once you have selected the appropriate option, the header row will be promoted and the table will be updated accordingly.

The M Code Behind Table.PromoteHeaders

The M code behind Table.PromoteHeaders is relatively simple. When you select “Use First Row As Headers”, Power Query generates the following M code:

This code takes the table that has been modified using the “Changed Type” function and promotes the first row to the header row. If you select “Use Headers As First Row” instead, the M code will look like this:

The “PromoteAllScalars=true” parameter tells Power Query to promote all scalar values in the first row to the header row, even if they are not currently in their own column.

If the table has multiple header rows, the M code will look like this:

The “PromoteHeaders=2” parameter tells Power Query to promote the first two rows to the header row.

Table.PromoteHeaders is a simple yet powerful function in Power Query that allows you to easily promote the first row of a table to the header row. This can help to make your data more organized and easier to work with. By understanding the M code behind Table.PromoteHeaders, you can gain a deeper understanding of how Power Query works and how you can use it to efficiently manipulate and transform your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us