Get last or previous row based on a condition

Hi Anonymous 

Thank you for the reply.
Please try this below query [Optimized version with less steps]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxC8IwEAXg/5K5cL67lnYtboKT3UqHqoGI1YKb/95LGgiJOoTA+8i9I+NoMJvKHFb31GvQIwTiHYuZqhx7PayUlM8aHefXPapQ/R9B4pG/0JeCScJgBJaLz2+PqB3BI7oS/dw27Is2WH3V5OTssrzjW90o1ebcb8VIvY3VaO9W++svSswmlwhsqnOnDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Status = _t, Record_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Status", type text}, {"Record_Date", type text}})

//{{"Record_Date", type date}}, "en-US"
,
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Record_Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"ID", Order.Ascending}, {"Record_Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    Custom1 = Table.TransformColumnTypes(Table.AddColumn(#"Added Index", "Nxt ID", each try #"Added Index"[ID]{ [Index] + 1 }
 otherwise null), {"Nxt ID", type text}),
    Custom2 = Table.TransformColumnTypes(Table.AddColumn(Custom1, "Nxt Status", each try Custom1[Status]{ [Index] + 1 }
 otherwise null), {"Nxt Status", type text}),
    Custom3 = Table.TransformColumnTypes(Table.AddColumn(#"Custom2", "Nxt Record_Date", each try #"Custom2"[Record_Date]{ [Index] + 1 }
 otherwise null), {"Nxt Record_Date", type date}),
    #"Added Custom" = Table.TransformColumnTypes(Table.AddColumn(Custom3, "Final Date", each if [ID] = [Nxt ID] 
then 
     if 
     [Status] = [Nxt Status] and [Status] = "T" 
     then [Record_Date]
     else if [Status] = "A" then [Record_Date] else [Nxt Record_Date] else null), {"Final Date", type date}),
    #"Added Custom1" = Table.Distinct(Table.SelectRows(Table.SelectColumns( Table.TransformColumnTypes(Table.AddColumn(#"Added Custom", "Final Status", each if [ID] = [Nxt ID] 
then 
     if 
     [Status] = [Nxt Status] and [Status] = "T" 
     then [Status]
     else if [Status] = "A" then [Status] else [Nxt Status] else null), {"Final Status", type text}), {"ID", "Name", "Final Date", "Final Status" }), each ([Final Date] <> null)),{"ID"})
in
    #"Added Custom1"

And the below query is for understanding purpose [Having more steps]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxC8IwEAXg/5K5cL67lnYtboKT3UqHqoGI1YKb/95LGgiJOoTA+8i9I+NoMJvKHFb31GvQIwTiHYuZqhx7PayUlM8aHefXPapQ/R9B4pG/0JeCScJgBJaLz2+PqB3BI7oS/dw27Is2WH3V5OTssrzjW90o1ebcb8VIvY3VaO9W++svSswmlwhsqnOnDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Status = _t, Record_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Status", type text}, {"Record_Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Record_Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"ID", Order.Ascending}, {"Record_Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Nxt ID" = Table.AddColumn(#"Added Index", "Nxt ID", each try #"Added Index"[ID]{ [Index] + 1 }
 otherwise null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Nxt ID",{"ID", "Nxt ID", "Status", "Record_Date", "Name", "Index"}),
    #"Added Nxt Status" = Table.AddColumn(#"Reordered Columns", "Nxt Status", each try #"Reordered Columns"[Status]{ [Index] + 1 }
 otherwise null),
    #"Added Nxt Record_Date" = Table.AddColumn(#"Added Nxt Status", "Nxt Record_Date", each try #"Added Nxt Status"[Record_Date]{ [Index] + 1 }
 otherwise null),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Nxt Record_Date",{"ID", "Nxt ID", "Status", "Nxt Status", "Record_Date", "Nxt Record_Date", "Name", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Nxt Record_Date", type date}, {"Nxt Status", type text}, {"Nxt ID", type text}}),
    #"Added Final Date" = Table.AddColumn(#"Changed Type1", "Final Date", each if [ID] = [Nxt ID] 
then 
     if 
     [Status] = [Nxt Status] and [Status] = "T" 
     then [Record_Date]
     else if [Status] = "A" then [Record_Date] else [Nxt Record_Date] else null),
    #"Added Final Status" = Table.AddColumn(#"Added Final Date", "Final Status", each if [ID] = [Nxt ID] 
then 
     if 
     [Status] = [Nxt Status] and [Status] = "T" 
     then [Status]
     else if [Status] = "A" then [Status] else [Nxt Status] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Final Status",{"Nxt ID", "Status", "Nxt Status", "Record_Date", "Nxt Record_Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Final Date] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"ID"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Index"})
in
    #"Removed Columns1"

Thank you.
If my answer helps you, please mark it as solution.