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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors