New Column Based on Other Column

[Jeevan1991]

Dear Friends,

I have below  data.

Essential or Non Essential	Date	Cafe & Date & Item Code	Stock Status
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Stock Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available

I want to arrive new column based on "Cafe & Date & Item Code" & "Stock Status"  & "Essential or Non Essential" column i..e. "New Status" column.

Essential or Non Essential	Date	Cafe & Date & Item Code	Stock Status	New Status
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Stock Available	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021W443	Not Available	Not Available

Column "Cafe & Date & Item Code" contains duplicate data and if "Essential or Non Essential" is "Essential" and "Stock Status" is "Not Avilable" and for any one of the "Essential" of "Cafe & Date & Item Code" column then "New Status" should be "Not Available" only.

For an example take a look at the first 3 rows.

Essential or Non Essential	Date	Cafe & Date & Item Code	Stock Status	New Status
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available	Not Available
ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Stock Available	Not Available
NON ESSENTIAL	19-Apr-21	1430070074/19/2021A2338	Not Available	Not Available

In this first two are essential but for first row "Stock Status" is "Not Available" and for second row "Essential" and stock status is "Stock Available", since the first one is already "not available" then this also should be "not available" only.

@Minakshi @Anonymous @Jihwan_Kim @amitchandak @parry2k @Geradav @PhilipTreacy @Kinjal @Sujit_Thakur 

[amitchandak · Accepted solution]

@Jeevan1991 , Try a new column like

new column =
var _cnt = countx(filter(Table, [Cafe & Date & Item Code] =earlier([Cafe & Date & Item Code]) && [Essential or Non Essential] = "ESSENTIAL" && [Stock Status] = "Not Available" ), [Cafe & Date & Item Code] ) +0
return
if( _cnt >0 , "Not Available" ,[Stock Status])

[Jeevan1991]

Thanks you so much @amitchandak  it worked wonders for me.