Forum Discussion
How do I promote headers except one column?
You could insert this code snippet after the step that creates your example table.
The code removes the first entry from all except the first column, and renames columns 2..n according to the entry in the first row.
//Promote headers except for first column #"Promote Headers2" = let colHdrs = {Table.ColumnNames(#"Previous Step"){0}} & List.Skip(Record.FieldValues(#"Previous Step"{0})), colDataRaw = List.Buffer(Table.ToColumns(#"Previous Step")), colData = {colDataRaw{0}} & List.Transform(List.Skip(colDataRaw), each List.Skip(_)) in Table.FromColumns(colData,colHdrs) in #"Promote Headers2"You should then set the appropriate data types for each column, and may possibly need to clean up the last row.
You could insert this code snippet after the step that creates your example table.
The code removes the first entry from all except the first column, and renames columns 2..n according to the entry in the first row.
//Promote headers except for first column
#"Promote Headers2" =
let
colHdrs = {Table.ColumnNames(#"Previous Step"){0}} & List.Skip(Record.FieldValues(#"Previous Step"{0})),
colDataRaw = List.Buffer(Table.ToColumns(#"Previous Step")),
colData = {colDataRaw{0}} & List.Transform(List.Skip(colDataRaw), each List.Skip(_))
in
Table.FromColumns(colData,colHdrs)
in
#"Promote Headers2"You should then set the appropriate data types for each column, and may possibly need to clean up the last row.
Using Power Query Editor:
Open Power Query Editor:Open the Power Query Editor in Power BI by clicking on Transform Data.
Promote Headers:Click on the table and select 'Home > Use First Row as Headers' to promote all headers.
Exclude the Column from Promotion:To exclude a column from the header promotion, you must modify it after the promotion:
Choose the column that you wish to exclude.
Manually rename the column by clicking the column header and choosing 'Rename' or by editing the 'Applied Steps'.
Manual Adjustment is an alternative approach:
Promote headers normally.
Revert the specific column to its original state by manually changing it.
Navigate to Advanced Editor.Locate the #'Promoted Headers' step.
Create a custom step to change the column's name or value to its original name or value.
Powerbi Example Code Snippet:-
Would you like further assistance on Power Query steps?
Nearly 3 week old thread however, consider:
PromotedHeaders = Table.PromoteHeaders(PreviousTable, [PromoteAllScalars=true]), RenamedColumns = Table.RenameColumns(PromotedHeaders,{{Table.ColumnNames(PromotedHeaders){0}, Table.ColumnNames(PreviousTable){0}}})
