Use M-query to find the last period (date) prior to employee gaining promotion and add it to row

[Syndicate_Admin]

Employee table below contain list of employee data which rolls repeatedly everymonth.

Note the following:
Current_employee_flag: When new employee joins, Current_employee_flag is greater than 1 but when emploee leaves it is 0. Therefore 1 in this column represent current employee this month.
Promotion column: 1 represent the month employee gained promotion while 0 means no promotion that month.

Example: employee_Id H123 in October no prmotion (Current_employee_flag = 1 and promotion = 0), She gained promotion H to G in November (Current_employee_flag = 1 and promotion = 1) but left the company in December (Current_employee_flag = 0 and promotion = 0)

Employee_ID	Period	Current_employee_flag	Promotion	Grade
A123	31/12/2023	1	0	E
B123	31/12/2023	1	0	G
C123	31/12/2023	1	0	F
D123	31/12/2023	1	0	G
E123	31/12/2023	1	0	E
F123	31/12/2023	1	1	E
G123	31/12/2023	1	0	G
H123	31/12/2023	0	0	G
I124	31/12/2023	1	0	H
A123	30/11/2023	1	1	E
B123	30/11/2023	1	1	G
C123	30/11/2023	1	0	F
D123	30/11/2023	1	0	G
E123	30/11/2023	1	0	E
F123	30/11/2023	1	0	F
G123	30/11/2023	1	1	G
H123	30/11/2023	1	1	G
A123	31/10/2023	1	0	F
B123	31/10/2023	1	0	H
C123	31/10/2023	1	0	F
D123	31/10/2023	1	0	G
E123	31/10/2023	1	0	E
F123	31/10/2023	1	0	F
G123	31/10/2023	1	0	H
H123	31/10/2023	1	0	H

The idea is to further calculate how long each employee have stayed on each grade before promotion.

For each employee who have gained promotion only (Current_employee_flag > 0 and Promotion = 1), I want M-query to find the 'last period' (date) prior to employee gaining promotion, and populate it in the promotion period new row called Last_grade_period?

The expected answer should be like below

Employee_ID	Period	Current_employee_flag	Promotion	Grade	Last_grade_period
A123	31/12/2023	1	0	E
B123	31/12/2023	1	0	G
C123	31/12/2023	1	0	F
D123	31/12/2023	1	0	G
E123	31/12/2023	1	0	E
F123	31/12/2023	1	1	E	30/11/2023
G123	31/12/2023	1	0	G
H123	31/12/2023	0	0	G
I124	31/12/2023	1	0	H
A123	30/11/2023	1	1	E	31/10/2023
B123	30/11/2023	1	1	G	31/10/2023
C123	30/11/2023	1	0	F
D123	30/11/2023	1	0	G
E123	30/11/2023	1	0	E
F123	30/11/2023	1	0	F
G123	30/11/2023	1	1	G	31/10/2023
H123	30/11/2023	1	1	G	31/10/2023
A123	31/10/2023	1	0	F
B123	31/10/2023	1	0	H
C123	31/10/2023	1	0	F
D123	31/10/2023	1	0	G
E123	31/10/2023	1	0	E
F123	31/10/2023	1	0	F
G123	31/10/2023	1	0	H
H123	31/10/2023	1	0	H

[Syndicate_Admin]

Does 

= Table.AddColumn(PREVIOUSSTEP, "Last_grade_period", each if [Promotion] = "1" then Date.AddMonths([Period], -1) else null)

give you the result you are looking for?

[Syndicate_Admin]

Apologies, I have modified the question to make it easier to understand

[Syndicate_Admin]

This example code should be the solution you are looking for.

let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc49DoAgDAXgqxhmkv6gEUZlMS66E+5/DSkJqQ1Dl/el7SvF3S9xcN5hm0BADIwtqF6JhBAwCfFMBBiEcCKOgHKQUqfnMr9w04ODxq9VtmKn47RbuzYcZBrSTG0rao38O7j05MwmqR8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, Promotion = _t, promotion_date = _t]),
    Custom1 = 
    Table.TransformColumns(Source, {{"promotion_date", each Date.FromText(_, "de-DE"), type date}}),
    #"Changed Type" = 
    Table.TransformColumnTypes(Custom1,{{"Employee_ID", type text}, {"Promotion", Int64.Type}, {"promotion_date", type date}}),
    Custom2 = 
    Table.Buffer(
        Table.Sort(
            #"Changed Type", 
            {{"promotion_date", Order.Descending}}
        )
    ),
    fxTableEdit = 
    (inputTable as table) as table =>
        let
            Source = inputTable,
            AddIndex = Table.AddIndexColumn(Source, "_index", 1, 1),
            AddLastDate = Table.AddColumn(AddIndex, "Last Promotion Date", each if List.Sum(AddIndex[Promotion]) > 0 then List.First(Table.SelectRows(AddIndex, (x)=> x[_index] = [_index] + 1)[promotion_date]) else null, type date),
            RemoveIndex = Table.RemoveColumns(AddLastDate, {"_index"})
        in
            RemoveIndex,
    #"Grouped Rows" = 
    Table.Group(
        Custom2, 
        {"Employee_ID"}, 
        {
            {
                "_nestedTables", 
                each fxTableEdit(_),
                type table [Employee_ID=nullable text, Promotion=nullable number, promotion_date=nullable date, Last Promotion Date=nullable date]
            }
        }
    ),
    #"Expanded _nestedTables" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "_nestedTables", 
        {"Promotion", "promotion_date", "Last Promotion Date"}, 
        {"Promotion", "promotion_date", "Last Promotion Date"}
    )
in
    #"Expanded _nestedTables"

[Syndicate_Admin]

You can do this by Grouping by ID and then Shifting the Last Promotion Date column up one.

Based on your first post, I am also checking the Promotion column to ensure there is a `1` in addition to a Promotion Date; but not sure if this is necessary

let

//change next line to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Employee_ID", type text}, {"Promotion", Int64.Type}, {"promotion_date", type date}}),

//Group by ID
    #"Group by ID" = Table.Group(#"Changed Type",{"Employee_ID"}, {

//Shift up the Last Promotion Date for the new column
//Based on your first post, also checking the Promotion column
//   but this may not be necessary based on your second post
        {"Last Promotion Date", (t)=>
            let 
                #"Shifted Date & Promotion" =  Table.FromColumns(
                    Table.ToColumns(t) &
                        {List.RemoveFirstN(t[Promotion],1) & {null}} &
                        {List.RemoveFirstN(t[promotion_date],1) & {null}},
                        Table.ColumnNames(t) & {"Shifted Promotion"} & {"Last Promotion Date"}),
                #"null Last Promotion Date" = Table.ReplaceValue(
                    #"Shifted Date & Promotion",
                    each [Shifted Promotion],
                    each [Last Promotion Date],
                    (x,y,z)=> if y=1 then x else null ,
                    {"Last Promotion Date"})
            in 
                #"null Last Promotion Date",
            type table[Employee_ID=text, Promotion=Int64.Type,promotion_date=date, Shifted Promotion = Int64.Type, Last Promotion Date=date]
                
                } 
    }),
    #"Expanded Last Promotion Date" = Table.ExpandTableColumn(#"Group by ID", "Last Promotion Date", {"Promotion", "promotion_date", "Last Promotion Date"})
in
    #"Expanded Last Promotion Date"

Source

Results

[Syndicate_Admin]

As I suggested in my previous answer, if you don't need to check the Promotion column, the code can be simplified to just grouping by ID and then shifting the Promotion Date column up by one.

In your new data, this will return the same result:

let

//change next line to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Employee_ID", type text}, {"Promotion", Int64.Type}, {"promotion_date", type date}}),

//Group by ID
    #"Group by ID" = Table.Group(#"Changed Type",{"Employee_ID"}, {

    //Shift up the Last Promotion Date for the new column
        {"Last Promotion Date", (t)=>
                 Table.FromColumns(
                    Table.ToColumns(t) &
                        {List.RemoveFirstN(t[promotion_date],1) & {null}},
                        Table.ColumnNames(t) & {"Last Promotion Date"}),
            type table[Employee_ID=text, Promotion=Int64.Type,promotion_date=date, Last Promotion Date=date]
                
                }}),

    #"Expanded Last Promotion Date" = Table.ExpandTableColumn(#"Group by ID", "Last Promotion Date", 
        {"Promotion", "promotion_date", "Last Promotion Date"})
in
    #"Expanded Last Promotion Date"

[Syndicate_Admin]

Thank you for attempting this however this did not do what I wanted. Maybe I am not explaining what I need properly.

See the image below which show how your query above is behaving with the table.
I have added a marker to the image to help explain what I want.

The date is supposed to populate on the row where the promotions = 1 only and should only pull the date from the last promotion where the promotions = 1 also.

The x is what your formula was pulling.
The arrow indicates what I want to do
The date on the red suppose to go into the row where the arrow is pointing.

[Syndicate_Admin]

This image show a much clearer example of what you are trying to accomplish. The code below should do the trick:

let

//change next line to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Employee ID", type text}, {"promotions", Int64.Type}, {"month_end_date", type date}}),

//Group by ID
    #"Group by ID" = Table.Group(#"Changed Type",{"Employee ID"}, {

//Shift up the Last Promotion Date for the new column
//Based on your first post, also checking the Promotion column
//   but this may not be necessary based on your second post
        {"Last Promotion Date", (t)=>
            Table.AddColumn(t,"Last promotions date", (r)=> if r[promotions] = 0 
                then null 
                else try Table.SelectRows(t, 
                            each [month_end_date] < r[month_end_date] and 
                            [promotions] = 1){0}[month_end_date] 
                    otherwise null),
                type table[promotions=Int64.Type, month_end_date=date, Last promotions date = nullable date]
                }}),
                
    #"Expanded Last Promotion Date" = Table.ExpandTableColumn(#"Group by ID", "Last Promotion Date", 
        {"promotions", "month_end_date", "Last promotions date"})
in
    #"Expanded Last Promotion Date"

[Syndicate_Admin]

Thank you so much.

This solution works and gives the column needed correctly however I have a table with more than 21 columns and this query removed every other column on the table except this four on your screen.

Is there a way I can do this without loosing other column on the table?

[Syndicate_Admin]

Double-click on the last step: #"Expanded Last Promotion Date" and see if those other columns are listed. If they are, it'd probably just be a matter of checking them off also. If not, we will need to modify the aggregation in Table.Group