Get last or previous row based on a condition

[Anonymous]

Hello PBI Community, 

I have historical employee data that is constantly chaging and would like to get one row per employee. But the condition that I need to meet is:

If status for the latest record_date is 'T', so need to get previous record for that employee. 

If status for the latest record_date is 'A', get that record. 

If status for the latest record_date is 'T', and previous record is also 'T', get latest record. 

Is this even possible? 

Here is a sample data

ID	Name	Status	Record_Date
1a	John	T	3/1/2023
1a	John	A	2/20/2023
2b	Mark	A	3/4/2023
2b	Mark	A	1/3/2022
2b	Mark	T	12/30/2021
3c	Tim	T	8/10/2018
3c	Tim	A	7/1/2017
4d	Shelly	T	4/2/2022
4d	Shelly	A	12/1/2021
5e	Choe	T	3/1/2023
5e	Choe	T	4/2/2022
5e	Choe	T	11/2/2021

An this is the what I would need to get

ID	Name	Status	Record_Date
1a	John	A	2/20/2023
2b	Mark	A	3/4/2023
3c	Tim	A	7/1/2017
4d	Shelly	A	12/1/2021
5e	Choe	T	4/2/2022

TIA

[VishalJhaveri]

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.

[Anonymous]

Thanks Vishal! 

It is perfect. Really appreciate your help

[VishalJhaveri]

Hello brother. @Anonymous 
Glad to know it worked for you.🙂

Can you please mark my answer as solution?

Thank you.

[VishalJhaveri]

Hello brother. @viviarc 
Glad to know it worked for you.

Can you please mark my answer as solution?

Thank you.

[VishalJhaveri]

Can you please validate the expected output. Please view my attached image.

Thank you.

[Anonymous · Accepted solution]

Hi VishalJhaveri, 

For 5e Choe either the first or the second record will be correct. The reason is that the Status for 5e Choe is  the same (T) for the records. 

Because in those scenarios (ID: 5e) either the first or the second can work, I'm not sure what it will simplify the process. 

Hope this makes sense. 

Thank you so much.