Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Frixel
Post Prodigy
Post Prodigy

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


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?

 

Colomn.png

1 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.

View solution in original post

13 REPLIES 13
spinfuzer
Super User
Super User

 

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]

 

 

spinfuzer_0-1703754710862.png

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"

 

 

 

 

 

@spinfuzer 

 

The first step i do this

Frixel_0-1703755747935.png

Then i get this

Frixel_1-1703755799103.png

 

Did you add the index column before the custom column?

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

Frixel_0-1703756159713.png

 And the second step, wher do i do that?

 

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
Super User
Super User

@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"








Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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

 

insert.png

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Frixel
Post Prodigy
Post Prodigy

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

Colomn2.png

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




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





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_0-1703753871721.png

 

@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 

Frixel_1-1703754202497.png

 

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

Then the name from this colomn is changed

 

Frixel_3-1703754349825.png

 

PijushRoy
Super User
Super User

Hi @Frixel 

Can you please share a screenshot of other columns header




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors