Getting the last date based on the next

[FlpsJw]

Hey, I think my problem is quite basic, but the solution that I build isn't performing very well.

I have a table with the following structure:

ID	NAME	JOB TITLE	STARTING DATE	TERMINATION
1	John	Analyst I	01/05/2020
1	John	Analyst II	05/03/2021
1	John	Analyst III	11/08/2024
2	Thomas	Engineer	01/09/2019
2	Thomas	Engineer II	12/04/2020
2	Thomas	Engineer III	06/09/2021	11/09/2022

I want to create another column called "Last Day" that will bring the last date before the person assume the new position, in case he is fired at the end, it must bring the termination date for the job role that he had when he was fired and if he still in the company, the Last Day of the current job must be null.

So, for example the table should look like this:

ID	NAME	JOB TITLE	STARTING DATE	TERMINATION	LAST DAY
1	John	Analyst I	01/05/2020		04/03/2021
1	John	Analyst II	05/03/2021		10/08/2024
1	John	Analyst III	11/08/2024		null
2	Thomas	Engineer	01/09/2019		11/04/2020
2	Thomas	Engineer II	12/04/2020		05/09/2021
2	Thomas	Engineer III	06/09/2021	11/09/2022	11/09/2022

Any idea how to do this on Power Query?

[wini_R · Accepted solution]

Hi @FlpsJw,
Please check if the following approach works better for you. 

You can paste the query to advanced editor in PQ to see how it works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc7BCoJAEAbgV1n2LOzOppHHDSU2cgWbm3jwIBnUCtmlt29GJeigp/kHPmb+upYuk5H0tshpnMujQIcXzle0FTp/EplF3jGvCuctutLLJqolsB/6QMOG9vEZ38JR1qB0oow2mhaxKieaKL1jCtuULdDZA9v4Zw2X6odnO1LIw+0euu61NEiJQrpN5xJglI7/+67pqfN+Pj51huWTMbJpvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"STARTING DATE", type date}, {"TERMINATION", type date}}),
    fxLastDate = (tb) => let
addIndx = Table.AddIndexColumn(tb, "index", 0,1)
in
Table.AddColumn(addIndx, "lastDate", each if [TERMINATION] <> null then [TERMINATION] else if [index] = List.Max(addIndx[index]) then null else Date.AddDays(addIndx[STARTING DATE]{[index]+1}, -1) ),
    GroupedRows = Table.Group(#"Changed Type", {"ID"}, {{"all", each _, type table [ID=nullable text, NAME=nullable text, JOB TITLE=nullable text, STARTING DATE=nullable date, TERMINATION=nullable date]}}),
    AddedLastDate = Table.AddColumn(GroupedRows, "lastDate", each fxLastDate([all])),
    #"Removed Columns" = Table.RemoveColumns(AddedLastDate,{"all"}),
    #"Expanded lastDate" = Table.ExpandTableColumn(#"Removed Columns", "lastDate", {"NAME", "JOB TITLE", "STARTING DATE", "TERMINATION", "lastDate"}, {"NAME", "JOB TITLE", "STARTING DATE", "TERMINATION", "lastDate.1"})
in
    #"Expanded lastDate"

Output table:

[FlpsJw]

@Omid_Motamedise  and @wini_R , both solutions worked just fine.

Thank you so much!!

[Omid_Motamedise · Accepted solution]

You need to use Table.SelectRows inside each otherand the thrik is that you can not use each _ for both of them, so you nead to replace it by custom function definition like (x)=> 

so the whole solution is as bellow (copy and past it into advance editor)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUo55iTmVxSUKnkC2gaG+gam+kYGRAZCjoBSrg10lWKmpvoExSKkhfqUgtYZAYy1Aak3gao2ArJCM/NzEYiDDNS89My81tQjqAkugUkNL/EohjjA00jcwQXUvLtVgN5tBDAe72RBqk5GRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NAME = _t, #"JOB TITLE" = _t, #"STARTING DATE" = _t, TERMINATION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NAME", type text}, {"JOB TITLE", type text}, {"STARTING DATE", type date}, {"TERMINATION", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last Day", each if [TERMINATION]<> null then [TERMINATION] else Date.AddDays(List.Min(Table.SelectRows(#"Changed Type", (x)=> x[STARTING DATE]>[STARTING DATE] and x[NAME]=_[NAME])[STARTING DATE]),-1))
in
    #"Added Custom"

it results in

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 
Thank you!

[wini_R · Accepted solution]

Hi @FlpsJw,
Please check if the following approach works better for you. 

You can paste the query to advanced editor in PQ to see how it works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc7BCoJAEAbgV1n2LOzOppHHDSU2cgWbm3jwIBnUCtmlt29GJeigp/kHPmb+upYuk5H0tshpnMujQIcXzle0FTp/EplF3jGvCuctutLLJqolsB/6QMOG9vEZ38JR1qB0oow2mhaxKieaKL1jCtuULdDZA9v4Zw2X6odnO1LIw+0euu61NEiJQrpN5xJglI7/+67pqfN+Pj51huWTMbJpvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"STARTING DATE", type date}, {"TERMINATION", type date}}),
    fxLastDate = (tb) => let
addIndx = Table.AddIndexColumn(tb, "index", 0,1)
in
Table.AddColumn(addIndx, "lastDate", each if [TERMINATION] <> null then [TERMINATION] else if [index] = List.Max(addIndx[index]) then null else Date.AddDays(addIndx[STARTING DATE]{[index]+1}, -1) ),
    GroupedRows = Table.Group(#"Changed Type", {"ID"}, {{"all", each _, type table [ID=nullable text, NAME=nullable text, JOB TITLE=nullable text, STARTING DATE=nullable date, TERMINATION=nullable date]}}),
    AddedLastDate = Table.AddColumn(GroupedRows, "lastDate", each fxLastDate([all])),
    #"Removed Columns" = Table.RemoveColumns(AddedLastDate,{"all"}),
    #"Expanded lastDate" = Table.ExpandTableColumn(#"Removed Columns", "lastDate", {"NAME", "JOB TITLE", "STARTING DATE", "TERMINATION", "lastDate"}, {"NAME", "JOB TITLE", "STARTING DATE", "TERMINATION", "lastDate.1"})
in
    #"Expanded lastDate"

Output table: