Groupby with a Condition

[Anonymous]

Wow. I didn't think this would be so involved. In Power Query Editor how can I convert the table below grouping Store Managers into two categories, if they failed an audit bucket as "Failed at least 1 Audit", if they have never failed, bucket them as "Passed all Audits":

Starting Table

Store Manager	Audit Violation
233	Failed Audit
233	Passed Audit
233	Passed Audit
238	Passed Audit
238	Passed Audit
240	Failed Audit
249	Passed Audit
249	Passed Audit
249	Passed Audit

Resulting End Table:

Store Manager	Audit Failed or Passed
233	Failed at least 1 Audit
238	Passed all Audits
240	Failed at Least 1 Audit
249	Passed all Audits

Help is much appreciated here. I couldn't believe this was so difficult.

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VtJRckvMzElNUXAsTcksUYrVgQkHJBYXEydsQYqwiQFWK00ssasmWjgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Manager" = _t, #"Audit Violation" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Store Manager"}, {{"Result", each let l = [Audit Violation], res = List.Accumulate(l, false, (s,c) => s or Text.Contains(c, "Failed", Comparer.OrdinalIgnoreCase)) in if res then "Failed at least once" else "Passed"}})
in
    #"Grouped Rows"

For fun only,