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

[PoweredOut]

Hi Experts

I would like to create a condional column.

I want to compare three columns from the same table and check if the values are the same in other rows.

Conditions are if [ID] = [ID](OtherRows) and  [Stage] = [Stage](OtherRows) and [Status] <> [Status](OtherRows), "Declined", "Approved"

I basically want to check if the Status is anything but approved based on the condions, it should be declined.

I hope this make sense

Thanks in advance

ID	Stage	Status	Wanted Condional Column
1	Open	Declined	Declined
1	Open	Approved	Declined
1	Open	Declined	Declined

[BA_Pete · Accepted solution]

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

[PoweredOut]

Thanks Pete