Forum Discussion
Custom Column in Power Query Editor - Transformation
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 |
Anonymous , 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())
1 Reply
Anonymous , 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())