Custom Column in Power Query Editor - Transformation

[Ramees_123]

I have 3 columns in my Power Query table.

Application Number, Stage & DateTime.

The table will be sorted based on DateTime. I need to create a custom column named 'FLAG' which get updated as 'Y' whenever there is a decrease in stage numer of column 'Stage' for specific application ID. 

In below example, we could see in the 4th row the Stage moved from 3 to 2 and so the flag got updated as Y. Also in 7th row the Stage moved from 4 to 2 and so so the flag got updated as Y. How to create this type of custom column ? Please help.

Application No	Stage	DateTime	FLAG
1	Stage 1	24/08/2021 8AM
1	Stage 2	24/08/2021 8.30AM
1	Stage 3	24/08/2021 9AM
1	Stage 2	24/08/2021 9.03AM	Y
1	Stage 3	24/08/2021 9.05AM
1	Stage 4	24/08/2021 9.30AM
1	Stage 2	24/08/2021 10AM	Y
1	Stage 3	24/08/2021 10.30AM
2	Stage 1	24/08/2021 8AM
2	Stage 2	24/08/2021 8.30AM
2	Stage 3	24/08/2021 9AM
2	Stage 2	24/08/2021 9.03AM	Y
2	Stage 3	24/08/2021 9.05AM
2	Stage 4	24/08/2021 9.30AM
2	Stage 2	24/08/2021 10AM	Y
2	Stage 3	24/08/2021 10.30AM

[amitchandak · Accepted solution]

@Ramees_123 , This is something you can get easily in DAX then power query. So in case you want DAX

new column

var _max = maxx(filter(Table, [Application No] = earlier([Application No]) && [DateTime] < earlier([DateTime])),[DateTime])
return
if( [Stage] <maxx(filter(Table, [Application No] = earlier([Application No]) && [DateTime] =_max),[Stage]), "Y", blank())