Forum Discussion
How do I promote headers except one column?
Hello everyone,
I need your help. I'm connecting my sharepoint to power bi, when I merged the file the headers turned out like this.
How can I promote headers except Source.Name which is already promoted?
Thank you!
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.
8 Replies
if your table names is Source, use this formula.
= Table.RenameColumns(Source,List.Skip(List.Zip({Table.ColumnNames(Source),Record.ToList(Source{0})})))
in the next step remove the first rowHello guys, I figured it out. The easiest was promoting the headers while still on the sample table.
Thank you so much for all your help!
so much easier than above solutions!
There can be various approaches. In my view, best would be below (refer to the sample code)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lFycgISzs5AwsVFKVYnWskQyDQCYmMgNlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]), FirstColumnName = Table.ColumnNames(Source){0}, #"Promoted Headers" = Table.PromoteHeaders(Source), FinalStep = Table.RenameColumns(#"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){0}, FirstColumnName}) in FinalStepUse Table.RenameColumns - you can use List.Zip to prepare the tuples.
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}}})
