Compare multiple date/time columns and find the latest date

[lightw0rks]

I have data similar to the following:

ID	Date1	Date2	Date3
1	2017/07/01 09:00:00	2020/09/10 12:00:00	2019/03/12 11:00:00
2	2017/07/01 10:00:00	null	null
3	2017/07/01 09:30:00	null	2020/05/03 09:00:00
4	2017/07/01 09:45:00	2020/08/03 09:35:00	null

• Date1 column always contains a valid date/time value
• Columns Date2 and Date3 may contain either a valid date/time or 'null'

I would like to create a new column [Last updated] containing the latest date/time value out of each of the 3 columns on a given row

ID	Date1	Date2	Date3	Last updated
1	2017/07/01 09:00:00	2020/09/10 12:00:00	2019/03/12 11:00:00	2020/09/10 12:00:00
2	2017/07/01 10:00:00	null	null	2017/07/01 10:00:00
3	2017/07/01 09:30:00	null	2020/05/03 09:00:00	2020/05/03 09:00:00
4	2017/07/01 09:45:00	2020/08/03 09:35:00	null	2020/08/03 09:35:00

I have tried using conditional columns but it seems to lack the fidelity to do something like this.

[Greg_Deckler]

@lightw0rks Best to unpivot probably but if not, MC Aggregations: https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

[ImkeF · Accepted solution]

Hi @lightw0rks ,

you can add a custom column with the following formula:

It allows you to add additional Date columns as well. It skips the first column of the table. 
But it could easily be changed to a solution where the date-columns are hardcoded if the dynamic solution is not desired.

This is the code to try out:

let
  Source = #table(
      {"ID", "Date1", "Date2", "Date3"}, 
      List.Zip(
          {
            {"1", "2", "3", "4"}, 
            {"01/07/2017 09:00", "01/07/2017 10:00", "01/07/2017 09:30", "01/07/2017 09:45"}, 
            {"10/09/2020 12:00", "null", "null", "03/08/2020 09:35"}, 
            {"12/03/2019 11:00", "null", "03/05/2020 09:00", "null"}
          }
        )
    ),
  #"Changed Type" = Table.TransformColumnTypes(
      Source, 
      {{"Date1", type datetime}, {"Date2", type datetime}, {"Date3", type datetime}}
    ),
  #"Added Custom" = Table.AddColumn(
      #"Changed Type", 
      "Custom", 
      each List.Max(List.Skip(Record.FieldValues(_)))
    )
in
  #"Added Custom"

[lightw0rks]

Thanks, that's great!

I needed a harcoded solution as I had a few more columns going on in my data so I modified it slightly:

List.Max(Record.FieldValues(Record.SelectFields(_, "Date1", "Date2", "Date3")))