Forum Discussion

lightw0rks's avatar
lightw0rks
Frequent Visitor
6 years ago
Solved

Compare multiple date/time columns and find the latest date

I have data similar to the following:

 

IDDate1Date2Date3
12017/07/01 09:00:002020/09/10 12:00:002019/03/12 11:00:00
22017/07/01 10:00:00nullnull
32017/07/01 09:30:00null2020/05/03 09:00:00
42017/07/01 09:45:002020/08/03 09:35:00null

 

  • 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

 

IDDate1Date2Date3Last updated
12017/07/01 09:00:002020/09/10 12:00:002019/03/12 11:00:002020/09/10 12:00:00
22017/07/01 10:00:00nullnull2017/07/01 10:00:00
32017/07/01 09:30:00null2020/05/03 09:00:002020/05/03 09:00:00
42017/07/01 09:45:002020/08/03 09:35:00null2020/08/03 09:35:00

 

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

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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"

     

3 Replies

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        lightw0rks
        Frequent Visitor
        6 years ago

        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")))