Forum Discussion
How to use Power Query List.Max for multiple columns?
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
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
3 Replies
Anonymous , Try like
= Table.AddColumn(#"Changed Type", "Custom", each List.Max({[Date 1],[#"Date 2 "],[Date 3]}))
Curly brackets did it!!!!!
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
