Forum Discussion
Power Query Use First Row as Headers for selected (or single) Row(s)
Hello folks,
How do I can promote for example 3 out of 10 or 1 out of 50 etc. rows to header? When I have a situation where I don't want promote every rows (they have their correct headers already). Please see picture below.
- G
I agree. It is amazing how this community works.
I didn't see any of those answers as a solution for my problem. I did it like this:
#"Promoted Headers Conditionally" = Table.TransformColumns(
#"Added Custom",
{
"Data",
each
if List.First(Table.ColumnNames(_)) = "Column1" then
Table.PromoteHeaders(_, [PromoteAllScalars=true])
else
_
}
12 Replies
Hi Gandhi
It’s a little bit confusing, if we’ve already changed the column headers for the first two columns, then what values should appear in the first row instead of “Header1” and “Header2”?Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡Gandhi I suggest this Power Query code to replace the headers of the first and second columns using the values from the first row, while preserving all data rows, including the first.
let
Source = your original table ,
FirstRow = Source{0},
OldColumnNames = Table.ColumnNames(Source),
NewColumnNames = {FirstRow[Column1], FirstRow[Column2]} & List.Skip(OldColumnNames, 2),
RenamedTable = Table.RenameColumns(Source, List.Zip({OldColumnNames, NewColumnNames}))
in
RenamedTableDid it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡Hi Gandhi
I'm not entirely sure I understood what you were explaining earlier.Did you have a chance to review the code I shared? If not, could you please share a screenshot of the step where the issue starts appearing?
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
Hi DataVitalizer
No, we've not changed the column headers for the first two columns. The situation appeared, when we brought SharePoint File with 3 different Sheets (that they will refresh dynamically when new sheet is added, you know the first one is "2023" the second one "2024" the third one "2025" and when "2026" comes, you don't need to do any manual work in Power Query that it will refresh there.
SO:
The problem:
When we expanded the Data column, the column headers of the SharePoint moved to first row (and the headers are "Column1", "Column2", ..., "Column n". BUT other column (like Item, Kind...) have the correct headers. Yes, we could delete them but we need "Item" or "Name" column that it will recognize which Sheet is 2023, 2024, 2025 etc. -> And with "Item" or "Name" column we can make new column "Year" and with it PBI knows which row is 2023, 2024 etc.
Did u get it? 😁
If you want to rename your existing column headers using values from a specific row (like row 3), don’t promote the whole row. Instead, keep only that row, convert it to a list, and then use Table.RenameColumns() with that list to rename the headers. This lets you selectively rename columns without touching the rest of the table. The image below gives you a step-by-step visual.
1 Keep Range of Rows
2 Convert to List
3 Reference the table
4 RenameColumns with converted list
The problem is that all headers IS NOT in the same row. Please see my picture at the post, there are some headers in row 1 and some headers in headers as it should be.
Hi Gandhi
If you want to promote a specific row (like the 2nd or 3rd row) as headers in Power Query, open Power Query by clicking Transform Data, then go to the Home tab and click on Advanced Editor. Replace Source with your existing step name and use the formula Table.PromoteHeaders(Table.Skip(Source, 1), [PromoteAllScalars=true]), where the number 1 means it will skip the first row and promote the second row as headers. You can change this number to 2 for the third row, 3 for the fourth, and so on. This method promotes your desired row as headers and removes the rows above it.
Hi Gandhi,
I would also take a moment to thank rohit1991, DataVitalizer for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Best Regards,
Harshitha.I agree. It is amazing how this community works.
I didn't see any of those answers as a solution for my problem. I did it like this:
#"Promoted Headers Conditionally" = Table.TransformColumns(
#"Added Custom",
{
"Data",
each
if List.First(Table.ColumnNames(_)) = "Column1" then
Table.PromoteHeaders(_, [PromoteAllScalars=true])
else
_
}Hi Gandhi,
Thank you for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank rohit1991 , DataVitalizer for actively participating in the community forum and for the solutions you have been sharing in the community forum. Your contributions make a real difference.
your idea to check if the first column is "Column1" and then only promote headers for those tables is perfect, especially when all sheets don't follow the same format. This way, you are not touching the sheets that already have correct headers, and still keeping important columns like "Item" or "Name" for year identification.
This kind of logic works very well when you are combining multiple dynamic sheets like 2023, 2024, 2025, etc, and you don’t want to manually adjust each time.
Hope this helps if you have any queries we are happy to assist you further.
Best Regards,
Harshitha.
