Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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
User | Count |
---|---|
120 | |
67 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |