Forum Discussion

Hoping's avatar
Hoping
Icon for Helper III rankHelper III
3 years ago

Max of date columns

I have 5 date columns in a table.

 

I would like to create a new column that shows the max of these 5 date columns.

 

Some of the rows could have null values for any or all of these dates. I want to perform a "max" ignoring teh null values.

 

If all 5 columns for a row are null, then maybe I would turn it into 31st December 9999.

 

Doing a List.Max on null values is causing an error.

 

How can this be acheived?

Need Help

1 Reply

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    3 years ago

    List.Max has no issues with nulls. My "Custom" column is finding the Max date from the 4 date columns, some with null.

    If you don't have nulls, where null is shown, those are empty, or "", you can filter that out with List.Select first. This does that:

     

                    List.Max(
                    List.Select(
                        Record.ToList(
                            Record.SelectFields(_, {"Date1", "Date2", "Date3", "Date4"})
                        ), 
                        each _ <> ""
                    )
                )