Using your sample data, except the MaxDate col....
Here's some M-Code that does what you want.
With more time I could probably optimize it a bit more:
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SetDataTypes = Table.TransformColumnTypes(Source,{{"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}, {"Date 4", type date}, {"Date 5", type date}}),
MaxDate = Table.AddColumn(SetDataTypes, "New Column Name",
each
if List.MatchesAny(List.Range(Record.ToList(_),0,5), each _ = null)
then null
else
List.Max(
List.Range(Record.ToList(_),0,5)
), type date)
in
MaxDate
Code:
Explanation:
- Record.ToList()...Converts the record values into a list
- List.Range()......Returns of sequence of list values
-beginning at the Col_Offset column (zero based)
-Including the number of columns indicated
Is that something you can work with?
Bookmarks