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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors