Power Query Use First Row as Headers for selected (or single) Row(s)

[Gandhi]

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

[v-hjannapu]

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.

[Gandhi · Accepted solution]

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
_
}

[v-hjannapu]

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.

[v-hjannapu]

Hi @Gandhi,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @Gandhi,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[rohit1991]

Hi@Gandhi

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

[Gandhi]

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. 

[rohit1991]

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.

[DataVitalizer]

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 💡

🟩 Follow me on LinkedIn

[Gandhi]

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? 😁

[DataVitalizer]

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

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

[DataVitalizer]

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 💡

🟩 Follow me on LinkedIn