Forum Discussion

juan-quinones's avatar
juan-quinones
New Member
2 years ago
Solved

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

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

"Dates"
"Power Query"
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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