Find the max date that is less than a specific date, considering a grouping factor (Power Query)

[juan-quinones]

Hi,

I have the following table in Power Query:

I need to add a column that follows the following logic: if Date1 = Date3 then Date1, otherwise find the maximum date in Date3 that is less than the Date1 of each row, considering the group ID. Is it possible to do this in Power Query?

Thanks

[dufoq3 · Accepted solution]

Hi @juan-quinones,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcvBDQAhCETRXjibiCCarcXQfxvL6GUS5vJfOEd09DpTm9JkYNo1EEyylRu51Shcd3LHf/lG8OeTHKNwPcgD/+UfwpLMHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, ID = _t, Date3 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date3", type date}}),
    Ad_Custom = Table.AddColumn(ChangedType, "Custom", each if [Date1] = [Date3] then [Date1] else List.Max(Table.SelectRows(ChangedType, (x)=> x[Date3] < [Date1])[Date3]), type date)
in
    Ad_Custom