Conditional Column, check values from other rows in the same table

Hi PoweredOut ,

You can group on [ID] and keep all other rows using the All Rows aggregator. Then just evaluate the nested table for the "Declined" keyword.

If you want to add more matching dimensions e.g. [Stage] etc., then just include thes with [ID] before you group.

Working example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM0DUi6pyTmZeakpSrE6KOKOBQVF+WWY4gj1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Stage = _t, Status = _t]),
    groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable text, Stage=nullable text, Status=nullable text, Wanted Condional Column=nullable text]}}),
    addStatusSummary = Table.AddColumn(groupID, "statusSummary", each if List.Contains([data][Status], "Declined") then "Declined" else "Approved"),
    expandDataCols = Table.ExpandTableColumn(addStatusSummary, "data", {"Stage", "Status"}, {"Stage", "Status"})
in
    expandDataCols

Example output:

Pete