Forum Discussion

paterke's avatar
paterke
New Member
2 years ago
Solved

Promote headers except a few columns

In power query I want to promote the first row to headers except 2 columns.  Any suggestions?
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
aduguid
Memorable Member
2 years ago

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's avatar
paterke
New Member
2 years ago

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. 

Column1Column2Year
ClientProduct2023
AA2023
  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    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's avatar
      paterke
      New Member
      2 years ago

      "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, ...