Find the most recent of three dates in a row for three columns with nulls

[Anonymous]

I'm hoping to find a way to make a column with the most recent of three dates in a row for three other columns with nulls

Lets say I have a table with dogs and dates they learned three tricks. Note that some dogs only learn one, two or zero tricks instead of all three tricks.

I'm hoping to add a column that shows the date of the most recent trick the dog learned (example result below):

I have explored a couple of potential issues that don't quite work for my situation, but I'm including them here in case they work for someone else.:

Pivoting and Group By:

1) Un-pivot the three trick columns (Sit, Speak, Roll Over), resulting in the below table:

M code:  #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Previous Step", {"Dog Name"}, "Attribute", "Value"),

2) Group By Dog name to find the most recent date associated with each dog. 

M code for this: #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Dog Name"}, {{"Most Recent Trick", each List.Max([Value]), type datetime}})

The problem: Buster (who never learned any tricks and has nulls in all three date columns) disappears from the table. I would like to keep Buster on the table with a null or "No tricks learned" in the Most Recent Trick column.

If Else

I'm finding that Power Query gives me errors if I compare nulls. 

One option would be to replace the nulls with a different value (such as #date(1901, 1, 1) so they could be compared but I'm nervous about altering the data in this manner.

This is my M code:

#"Added Conditional Column" =

Table.AddColumn(#"Previous Step",
"Most Recent Trick",
each if
[Sit] >= [Speak]
and [Sit] >= [#"Roll Over"]
then [Sit]
else if
[Speak] >= [#"Roll Over"]
and [Speak] >= [Sit]
then [Speak]
else if
[#"Roll Over"] >= [Speak]
and [#"Roll Over"] >= [Sit]
then [#"Roll Over"]

else null)

I have also tried using Number.From() to compare the trick dates as numbers instead of dates:

#"Added Conditional Column" = Table.AddColumn(#"Removed Columns",
"Most Recent Trick",
each if
Number.From([Sit]) >= Number.From([Speak])
and Number.From([Sit]) >= Number.From([#"Roll Over"])
then [Sit]
else if
Number.From([Speak]) >= Number.From([#"Roll Over"])
and Number.From([Speak]) >= Number.From([Sit])
then [Speak]
else if
Number.From([#"Roll Over"]) >= Number.From([Speak])
and Number.From([#"Roll Over"]) >= Number.From([Sit])
then [#"Roll Over"]

else null)

Does anyone have a different solution in mind that could work for Power Query? My ideal solution would look something like this:

- Would result in a new column that gives me the most recent date of the three tricks the dog learned for each row

- Keep all of the dogs on the table (including the dog with null values in the other three columns)

- Would not give me errors for comparing nulls

- Would not require me to alter the data (ie by replacing the nulls with a 1/1/1901 or other fake date)

This is a table you can copy and paste for dummy data if you would like:

Dog Name	Sit	Speak	Roll Over
Bruno	1/2/2015	2/14/2015	4/5/2015
Jim	5/4/2021
Harold	3/1/2015	3/1/2015
Ollie			8/1/2015
Minnie		9/29/2015
Lulu		12/13/2015	12/1/2021
Buster
Fido		7/6/2015

[HotChilli · Accepted solution]

Try a custom column

List.Max({[Sit],[Speak], [Roll Over]})

[Anonymous]

This worked perfectly! I used the UI to create a custome column and copied and pasted your code into the box. 

Before I had tried a solution with List.Max({...}) but I was typing it directly into Advanced Editor and I think I didn't get the code right. I am still very new to PQ and M code so it was probably an error on my part before.

If anyone else in a similar situation as me (new to M, needs a solution like mine, tried to code with List.Max in the Advanced Editor before and got errors), I encourage you to use the Custom Column button on the UI and just copy and paste the List.Max code in there.