Not all colomns 'use the first line as a header' possible?

[Frixel]

Hi all,

If I choose the option to use the first line as a header, that is fine for columns 2 to 10.
But column 1 is the Source.name column and I want to keep it that way.

If I choose the option to use the first line as a header, the header of column 1 becomes something like '20231227090215_export.xls'

Is there a way to not change the first column?

[spinfuzer]

1. Simplest way is probably to Add Index Column.

2. Now add a Custom Column with formula 

if [Index] < 1 then "Source.Name" else [Source.Name]

3. Change from <1 to <2 or however many rows you need.

4. Remove index and old Source.Name column.

5. Promote headers and reorder as necessary.

Full Example Below.  Copy and Paste into a blank query and follow the applied steps to see how it is done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcs7PKc3N80vMTTVE4RkpxerA1IBkTJD4RkBsisQ3BmIzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] < 1 then "Source.Name" else [Source.Name]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Source.Name", "Custom", "Column1", "Column2", "Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reordered Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"abc", type text}, {"Source.Name", type text}, {"ColumnName1", Int64.Type}, {"ColumnName2", Int64.Type}, {"0", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"abc", "0"})
in
    #"Removed Columns"

[Frixel]

@spinfuzer 

The first step i do this

Then i get this

[spinfuzer]

Did you add the index column before the custom column?

[Frixel]

Now i do that yes 👍 and i have this for now

 And the second step, wher do i do that?

[spinfuzer · Accepted solution]

You are finished now. Remove any unwanted columns (the index column and the old source.name column) and THEN use first row as headers.

[Fowmy]

@Frixel 

Please check the attached file. You can add a rename of the first column and automate it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIE4hKlWJ1opSQgywiIK8G8ZCDLBIhLwbwUIMsUiDPBvFQgywyI85ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ToCol = Table.ColumnNames(Source){0},
    FromCol = Table.ColumnNames(#"Promoted Headers"){0},
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{FromCol, ToCol}})
in
    #"Renamed Columns"

[Frixel]

I don`t know how to add this into my model.

When i insert it at the query settings then this error.

[Fowmy]

@Frixel 

First, promote the header as it is then add the following steps
// Replace Source with the step before Promote Headers operation
ToCol = Table.ColumnNames(Source){0}, 
FromCol = Table.ColumnNames(#"Promoted Headers"){0},
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{FromCol, ToCol}})

[Frixel]

This is what i can do.
Row 2 must be the headers for colomn 2 etc

[PijushRoy]

Hi @Frixel 

As your data, column1 to column6, 1st row is blank
You can use "1st row as header"
Then you can double-click on each header and rename the column header.
If a dataset schema is static, it will solve your requirement.
Please let me know

[Frixel]

Yes that is what i have to do but after a few day`s  and i will refresh the model then there is this error

[Frixel]

@PijushRoy 
if you see that when i mean with the option ' use the first line as a header'
Here is the name what i want 

But when i do ' use the first line as a header'

Then the name from this colomn is changed

[PijushRoy]

Hi @Frixel 

Can you please share a screenshot of other columns header