Use particular row as header

[jeongkim]

Hi,

Can we use rows which has particular value as Header? instead of first row?

As I combined multiple files so if I use first row, then only 1 row from 1 file has header and rest of files have no header. 

e.g. hope to have 'id' will be header in matrix/table visual

Query

Visual

Of course I can manually change the column name as 'id' and filter not to select 'id' values but the thing is I have 100 columns for other data so it would be really hard to change all the column names. 

And sometimes the column order changes so I try to avoid to fix column name by order. 

[Anonymous · Accepted solution]

Hi @jeongkim ,

If you are making a change to a row I think you can do it in Power Query by first deleting all the rows above the row you want to use as a title. If there is only the desired row at the top, use the "Use first row as Headers " option to elevate that row to a title.

In case of multiple columns, I think you can use M-code, the following M-code will hopefully help you.

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    PromoteHeaders = Table.PromoteHeaders(
        Table.Skip(Source, 
            List.PositionOf(
                List.Transform(Source[Column1], Text.Upper), 
                "ID"
            )
        )
    )
in
    PromoteHeaders

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.