Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Use particular row as header

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. 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.