How to create a custom column based on the conditions between two columns?

Question

I'm using Power Query in Power BI to create a custom column "STATUS CHECK" as shown in the table below.

ID	STATUS	STATUS CHECK
A1	Pending	Terminated
A1	Completed	Terminated
A3	Terminated	Terminated
A4	Completed	Completed
A4	Pending	Pending
A2	Pending	Pending
A1	Terminated	Terminated
A2	Completed	Completed
A2	Completed	Completed
A3	Completed	Terminated
A3	Completed	Terminated

Case: For an ID there are multiple STATUS'. If any on of the STATUS for an ID = "Terminated" then we need to tag all the Status instances of that ID in a new column "STATUS CHECK" as "Terminated" else return "STATUS".

Please help me to create this column in Power Query. Dax is an easier solution but its disrupting my model which uses UNION in multiple sheets hence stuck with the problem.

Answer 1

Add a custom column and enter the following:

let a = Table.SelectRows(#"Changed Type", (x)=> x[ID] = [ID] and x[STATUS] = "Terminated"),
b = if Table.RowCount(a) > 0 then "Terminated" else [STATUS]
in b

#"Changed Type" is the name of your previous step