Forum Discussion
Helper IIIReference a column by number not name
I have a table that imports data from a spreadsheet. The first transform step is to change the column names. However the first cell name will always differ based on the date the excel is run so I would like to instead use column number rather than name to reference it. I could rename to "Column1" but I would need to delete that first cell every time I save the spreadsheet which seems cumbersome.
Actually I have worked it out you add the following near the start:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Column1"}})
2 Replies
- burakkaragoz
Super User
Hi 83dons ,
Yeah, this is a common issue when importing Excel files where column headers change dynamically, especially if they include dates.
If you're using Power Query in Power BI or Excel, you can reference a column by its position instead of its name using the Table.Column function like this:
Table.Column(#"Previous Step", 0)
This will return the first column regardless of its name. If you want to keep it as part of the table instead of a list, you can use:
Table.SelectColumns(#"Previous Step", {Table.ColumnNames(#"Previous Step"){0}})This way, you’re dynamically selecting the first column by index, which avoids issues when the header name changes.
Let me know if you're doing this in Power BI or Excel and I can tailor the steps more precisely.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI 
83donsActually I have worked it out you add the following near the start:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Column1"}})
