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]

The expand step only include the 4 columns showed above, it does not include the rest of the table.

[Syndicate_Admin]

In the editor, change that last step to read as follows:

#"Expanded Last Promotion Date" = Table.ExpandTableColumn(#"Group by ID", "Last Promotion Date", 
        {"promotions", "month_end_date", "Last promotions date"} 
        & List.RemoveMatchingItems(Table.ColumnNames(Source),
                {"promotions", "month_end_date", "Last promotions date","Employee ID"}))

You may need to re-set the data types of the extra columns.

[Syndicate_Admin]

It worked.

Yes. I need to reset the data type of the other columns.

You are star. Thank you so much.

[Syndicate_Admin]

@ronrsnfld @jgeddes 

Is there any more thing you can do to help me correct this query?

I applied your add column (Last_promotion_date) solution above to a large dataset and upon further investigation, I realise that it does not quite work well as it should.
It got majority of the data right but some wrong.
It correctly adds Last_promotion_date to all the Employee with promotions on the table but wrongly assigned some date to some with more than 2 rows.
see below result.
The ones on red with x mark were added correctly on the 2nd row of that employee_Id however it is wrong on the 3rd row of same employee Id.
Example Emp_id 486 suppose to have Last_promotion_date column as; row1 null (got it correct), row2 31 May 2017 (got it correct) and row3 31 January 2020(got it wrong)

The one with black ticks were added correcly.

[Syndicate_Admin]

The data source you show is quite different from the others you have shown in that every row of the second column contains a `1`.  

I have found that problems arise when the data sample presented differs significantly from the actual data set. I doubt that your most recent data set with all `1`'s reflects accurately your actual data.

If it does not, I won't be able to assist you unless you can provide a data set -- as text which can be copy/pasted; NOT as a screenshot -- that reproduces the error you are seeing.

[Syndicate_Admin]

Hi @Solution_b,

Result

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]),
    fn_Shift =
        (tbl as table, col as text, shift as nullable number) as table =>
    //v poslednom parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_NextValue"} 
                    else              {col & "_PrevValue"} ))
        in
            c,
    ChangedType = Table.TransformColumnTypes(Source,{{"promotion_date", type date}}, "sk-SK"),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(AddedIndex, {"Employee_ID"}, {{"All", each Table.Sort(_, {{"promotion_date", Order.Descending}}) , type table}}),
    Ad_PrevDateToAll = Table.TransformColumns(#"Grouped Rows", {{"All", each fn_Shift(_, "promotion_date", -1), type table}}),
    CombinedAll = Table.Combine(Ad_PrevDateToAll[All]),
    SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns,{{"promotion_date_PrevValue", "Last promotion date"}}),
    ChangedType1 = Table.TransformColumnTypes(RenamedColumns,{{"Last promotion date", type date}})
in
    ChangedType1