Replace the latest date in a column with a specific text

[kishanr]

Hi,

I have imported an excel file into power bi and using power query to clean my data. The image below shows that my first column has dates. This column contains the latest date in my data, 31/12/2023, in the first row as well as the last row. I want to replace the first row value of the latest date with the text 'Date' and then will promote the first row to headers so my column has heading Date.

I want to make this step dynamic as every month this excel file is updated, it will contain the latest date in the first row as well as the last row.

Could anyone help with this?

Thanks in advance

[collinsg · Accepted solution]

Good day kishanr,

If it is always the first row you want to promote to headers an approach is:

1. Go ahead and promote the first row to headers,
2. Then, rename the first column by refering to it by position rather than name.

After using the GUI to promote the first row (and assuming this step is called "Promoted Headers"), the code to rename the column would be (assuming the date is in the first column i.e. the column of index 0).

 #"Rename Column" = Table.RenameColumns( #"Promoted Headers", {{Table.ColumnNames(#"Promoted Headers"){0},"Date"}} )

Hope this helps.