Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
viviarc
Frequent Visitor

Get last or previous row based on a condition

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

IDNameStatusRecord_Date
1aJohnT3/1/2023
1aJohnA2/20/2023
2bMarkA3/4/2023
2bMarkA1/3/2022
2bMarkT12/30/2021
3cTimT8/10/2018
3cTimA7/1/2017
4dShellyT4/2/2022
4dShellyA12/1/2021
5eChoeT3/1/2023
5eChoeT4/2/2022
5eChoeT11/2/2021

 

An this is the what I would need to get

IDNameStatusRecord_Date
1aJohnA2/20/2023
2bMarkA3/4/2023
3cTimA7/1/2017
4dShellyA12/1/2021
5eChoeT4/2/2022

 

TIA

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

View solution in original post

6 REPLIES 6
VishalJhaveri
Resolver III
Resolver III

Hi @viviarc 

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.


Thanks Vishal! 

It is perfect. Really appreciate your help

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

Can you please mark my answer as solution?

VishalJhaveri_0-1679103097099.png

 

Thank you.

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

VishalJhaveri_1-1679103147503.png

 



Can you please mark my answer as solution?

VishalJhaveri_2-1679103148193.png

 

 

Thank you.

VishalJhaveri
Resolver III
Resolver III

VishalJhaveri_1-1678799609607.png

 

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

Thank you.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors