Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kishanr
Frequent Visitor

Replace the latest date in a column with a specific text

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.

kishanr_0-1705609425574.png

 

Could anyone help with this?

 

Thanks in advance

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

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.

View solution in original post

1 REPLY 1
collinsg
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors