Table.DemoteHeaders

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.DemoteHeaders

What is the Table.DemoteHeaders function?

The Table.DemoteHeaders function is a Power Query M function that is used to demote the header row of a table to a regular data row. When you import data into Power Query, the first row of the data is automatically promoted to a header row. This is useful when you want to work with the data in Excel, but it can be a problem when you want to use the data in Power Query.

The Table.DemoteHeaders function is used to solve this problem. It takes a table as input and returns a new table with the header row demoted to a regular data row.

The M code behind the Table.DemoteHeaders function

The M code behind the Table.DemoteHeaders function is relatively simple. Here is the code:

(table as table) as table =>

let

header = Table.PromoteHeaders(table),

demoted = Table.AddIndexColumn(header, "Index", 0, 1),

newHeaders = Table.ColumnNames(demoted){0},

newTable = Table.Skip(demoted, 1),

renamed = Table.RenameColumns(newTable, {{newHeaders, "Header"}}),

reordered = Table.ReorderColumns(renamed, {"Index", "Header", Table.ColumnNames(renamed){2..}})

in

reordered

Let’s break down this code step by step.

1. The first line of the code defines the input parameter of the function, which is a table.

2. The second line of the code promotes the header row of the table to a header row.

3. The third line of the code adds an index column to the table.

4. The fourth line of the code gets the name of the new header column.

5. The fifth line of the code skips the first row of the table, which contains the header row.

6. The sixth line of the code renames the new header column to “Header”.

7. The seventh line of the code reorders the columns of the table so that the index column comes first, followed by the header column, and then the remaining columns in their original order.

How to use the Table.DemoteHeaders function

Using the Table.DemoteHeaders function is very simple. Here is an example:

Suppose you have the following table in Power Query:

| Column1 | Column2 | Column3 |

|---------|---------|---------|

| Header1 | Header2 | Header3 |

| Data1 | Data2 | Data3 |

| Data4 | Data5 | Data6 |

To demote the header row, you simply need to call the Table.DemoteHeaders function on this table. Here is the code:

Table.DemoteHeaders(#"Previous Step")

This will return the following table:

| Index | Header | Column1 |

|-------|---------|---------|

| 0 | Header1 | Data1 |

| 1 | Header2 | Data2 |

| 2 | Header3 | Data3 |

| 3 | Header1 | Data4 |

| 4 | Header2 | Data5 |

| 5 | Header3 | Data6 |

As you can see, the header row has been demoted to a regular data row, and a new index column has been added to the table.

The Table.DemoteHeaders function is a powerful function in Power Query that allows you to easily demote the header row of a table to a regular data row. The M code behind this function is relatively simple, but it is important to understand how it works if you want to use it effectively. By following the steps outlined in this article, you should have a better understanding of how the Table.DemoteHeaders function works and how to use it in your own Power Query projects.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)