Add custom date column containing the current date

[jk100]

Hi,

I'm trying to create a custom column that adds the current date. The current date is the Date column with the highest number.
In my example for Project1 the current date can be found in column [Date3] = 03.01.2023.
However, for Project2 the current date can be found in column [Date2] = 03.02.2023.


With added current date column, but wrong value for Project2:


Does anyone know a solution on how to deal with this issue?

My sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyMNQHIiMDIyMQx0jfwATEMQZxjKEyII6xUqwOXB9MqSGyUiMYB4iMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Date1 = _t, Date2 = _t, Date3 = _t, #"Number of Dates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Number of Dates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Current Date", each [Date3])
in
    #"Added Custom"

[artemus · Accepted solution]

Oh, I misunderstood you. Then the custom column would look like:

[Date3] ?? [Date2] ?? [Date1]

[artemus]

Your custom column would look like:

List.Max({[Date1], [Date2], [Date3]})

[jk100]

Thank you for your reply!

Unfortunately for Project1 the column [Date3] has an earlier date than [Date2] and it should pick the date in the column with the highest number, so List.Max() does not work here.

[Anonymous]

I guess the List.Max should work.
Can you please check the date format of all the date column used here. Is it dd/mm/yyyy or mm/dd/yyyy?