Forum Discussion
Power Query Use First Row as Headers for selected (or single) Row(s)
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
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? 😁