How to use Power Query List.Max for multiple columns?

[Anonymous]

This thread on the forums shows how to use List.Max on a date column . What I need is to take the List.Max, not just for one column, but for values across 3 different columns. Can this be done?

Example data:

Date 1	Date 2	Date 3
8/1/20	8/2/20	8/3/20
8/4/20	8/5/20	8/5/20

Objective here is to create a 4th custom column that finds the max date in the rows to the left of it. For the first row of the custom column, we'd have a Max date of 8/3/20

Here's what I tried:

List.Max(#"Audit Log 2020"[Date 1],
   List.Max(#"Audit Log 2020"[#"Date 2"],
     List.Max(#"Audit Log 2020"[#"Date 3"])
        )
)

As can be seen, I'm trying to take the max of 3 different date columns

[AlB · Accepted solution]

Hi @Anonymous 

It would be better to pivot the columns but if you want to do it like this, paste this M code in  a blank query to see the steps. The last one is the relevant one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t, Date3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", Int64.Type}, {"Date2", Int64.Type}, {"Date3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(Record.ToList(_)))
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Anonymous , Try like

= Table.AddColumn(#"Changed Type", "Custom", each List.Max({[Date 1],[#"Date 2 "],[Date 3]}))