How do I promote headers except one column?

[stephchnva]

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!

[stephchnva]

Hello 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!

[ronrsnfld · Accepted solution]

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.

[p45cal]

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}}})

[dataflip]

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?

[Omid_Motamedise]

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 row

[lbendlin]

Use   Table.RenameColumns  - you can use List.Zip to prepare the tuples.

[Vijay_A_Verma]

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
    FinalStep