Use M-query to find the last promotion date and add it to new column

[Solution_b]

Apologies, Let me modify the data to make it easier to understand the question.
I have table below.

Employee_ID	Promotion	promotion_date
JP123	0	31/12/2023
JP123	1	30/09/2022
JP123	1	31/03/2020
JP123	1	28/02/2019
OH123	0	31/05/2022
OH123	1	30/04/2018
AB123	0	31/07/2023
AB123	1	30/09/2021
AB123	1	31/08/2019
CB123	0
BC123	0

I want M-query for the new column Last promotion date

Employee_ID	Promotion	promotion_date	Last promotion date
JP123	0	31/12/2023	30/09/2022
JP123	1	30/09/2022	31/03/2020
JP123	1	31/03/2020	28/02/2019
JP123	1	28/02/2019
OH123	0	31/05/2022	30/04/2018
OH123	1	30/04/2018
AB123	0	31/07/2023	30/09/2021
AB123	1	30/09/2021	31/08/2019
AB123	1	31/08/2019
CB123	0
BC123	0

The logic is: if you have 2nd promotion, populate the new column with the previous promotion date that is your 1st promotion date (same promotion row only). If you have 3rd promotion, populate the new column (same promotion row only) with the previous promotion date which is 2nd promotion date. if no promotion on that month leave new column blank.

[ronrsnfld · Accepted solution]

Your most recent 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"

[dufoq3]

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

[ronrsnfld]

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

[Solution_b]

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.

[ronrsnfld · Accepted solution]

Your most recent 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"

[Solution_b]

@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.

[ronrsnfld]

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.

[Solution_b]

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?

[ronrsnfld]

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

[Solution_b]

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

[ronrsnfld · Accepted solution]

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", "Employee ID"}))

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

[Solution_b]

It worked.

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

You are star. Thank you so much.

[jgeddes]

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"

[jgeddes]

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?

[Solution_b]

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