Group by MIN Date after Threshold

hi joshua1990 ,

you can try this out.

let
// Source table
Source = YourTableNameHere,

// Group by Article
GroupedTable = Table.Group(Source, {"Article"}, {
{"FilteredRows", each
let
// Filter rows where Value >= 0.9
Filtered = Table.SelectRows(_, each [Value] >= 0.9),
// If there are matching rows, get the MIN Date; otherwise, take the MAX Date
Result = if Table.RowCount(Filtered) > 0
then Table.First(Table.Sort(Filtered, {"Date", Order.Ascending}))[Date]
else Table.First(Table.Sort(_, {"Date", Order.Descending}))[Date]
in
Result
}
}),

// Rename and expand the result
ExpandedTable = Table.TransformColumns(GroupedTable, {{"FilteredRows", each _, type date}})
in
ExpandedTable

Hi joshua1990, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMjExBHx1wpVgerhAVCwhhFwhIs4QRiG2HRAZYwQ5UAysQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Article"}, {{"Date", each 
        [ a = Table.SelectRows(_, (x)=> x[Value] >= 0.9),
          b = if Table.IsEmpty(a) then List.Max([Date]) else a{0}[Date]
        ][b], type date}})
in
    GroupedRows