Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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.
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"
Did you add the index column before the custom column?
Now i do that yes 👍 and i have this for now
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.
@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"
⭕ 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.
@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}})
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
This is what i can do.
Row 2 must be the headers for colomn 2 etc
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
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
@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
Hi @Frixel
Can you please share a screenshot of other columns header
Proud to be a Super User! | |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
26 | |
14 | |
14 | |
12 |