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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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