Forum Discussion
Promote headers except a few columns
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 TeamIf 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!
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"
"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, ...