Is there a way of merging columns and replacing values by using the latest column name?

[H_insight]

Hello,

Is there a way of merging columns and replacing values by using the latest column name? Currently, the data does not hold any date information. (I guess I can extract the date from the column name).

The goal is when a new file is uploaded (i.e. Mar), the new data will replace all old data (i.e. Feb). I have tried to merge columns, but I can see values for all previous data.

Columns Example:

Column Header

FY22 Actual (Jan)

FY22 Actual (Feb)

FY22 Actual (Mar)

FY22 Notes (Jan)

FY22 Notes (Feb)

FY22 Notes (Mar)

Sample File

Many thanks

[Vijay_A_Verma · Accepted solution]

I think best way out would be to filter on File Name column which is a date column where #"Changed Type" should be replaced with previous step name

= Table.SelectRows(#"Changed Type", each ([File Name] = List.Max(Table.Column(#"Changed Type","File Name"))))

[H_insight]

Thanks @Vijay_A_Verma . I have used your input with a tweak to deliver the desired output.