Adding the last date to a new row as a new column in M for the same ID

[BarisT]

Dear Power BI users,

I would like to add the enddate as a last enddate in the newest row for each employee that has the same employeeID (22671)with. How can I add a new column to perform the added column "Last enddate" that is always extracted from the previous row considering the same EmployeeID (22671)? I did this now by hand, but it should take the enddate of the previous row. The link to the power BI example is here: https://1drv.ms/u/s!Al86B9l3mXfCcDTX7RtpkYNR7pk?e=T4QvHO

Thank you for your help in advance. Much greats.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYtBDsAgCAT/wllCWcC2b2n8/zcqVRPTExtm5nlIvapRIaCe2q9xZRxAn9RKcg/d+M22OIJj7BSzza+KyhQUS16CfQJkYLnkF/sWB/uM2ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EployeeID = _t, Startdate = _t, Enddate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EployeeID", Int64.Type}, {"Startdate", type date}, {"Enddate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EployeeID"}, {{"Max", each List.Max([Startdate]), type nullable date}, {"Last Enddate", each List.Min([Enddate]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"EployeeID", "Startdate"}, #"Grouped Rows", {"EployeeID", "Max"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Last Enddate"}, {"Last Enddate"})
in
    #"Expanded Grouped Rows"

[BarisT]

Hello,

No this is not what i want. You are now using last enddate as a column, but the exercise was to create that with only start and enddate. I hope I could explain it.

[Vijay_A_Verma]

I have generated output as per your picture. So, I am wondering what output do you need.