Promote headers except a few columns

[paterke]

In power query I want to promote the first row to headers except 2 columns. 

Any suggestions?

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from aduguid.

Hi @paterke ,

Here l provide another idea, please follow the steps below:

1. Duplicate the original table in Power Query Editor:

2. Select Year and Name and remove these columns:

3. Use first row of the duplicated table as headers:

4. Merge Table and Table(2).

5. Click on the button and select Year and Name to expand the table:

6.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[aduguid]

You you use something like this

let
    Source = /* Your data source step here */,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Reverted Headers" = Table.RenameColumns(#"Promoted Headers",{
        {"NewColumn1Name", "Column1"},
        {"NewColumn2Name", "Column2"}
    })
in
    #"Reverted Headers"

[paterke]

Thx

the problem is that the value promoted to a header can change. 

My table looks like this. But after promoting the row to headers I won't have a Year column anymore. I can change the column 2023 back to Year, but this could change to 2024 on the next refresh. 

Column1	Column2	Year
Client	Product	2023
A	A	2023

[aduguid]

Taking that into consideration try this

let
    // Load your data
    Source = Excel.Workbook(File.Contents("your_file_path.xlsx"), null, true),

    // Promote the first row to headers
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Get the column names
    columnNames = Table.ColumnNames(#"Promoted Headers"),

    // Identify the column that contains the year
    yearColumnName = List.First(List.Select(columnNames, each Text.IsNumeric(_))),

    // Rename the identified year column to "Year"
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers", {{yearColumnName, "Year"}})
in
    #"Renamed Columns"

[paterke]

"Expression.Error: The name 'Text.IsNumeric' wasn't recognized. Make sure it's spelled correctly." 😞

And this is a solution for 1 column. What if I have multiple columns: Year, Name, ...