Get Latest Entry

[Anonymous]

Hey everyone, 

I have a new problem to solve. My data looks like this:

Serialnumber	LastModified	ProductID
134567	1. 2. 2020	THXY22
1869999	1. 2. 2020	THXY22
347584	1. 2. 2020	THXY22
177777	4. 2. 2020	THXY24
178888	7. 6. 2020	THXY24
389999	4. 2. 2020	THXY24
188888	10. 2. 2020	THXY26
1833445	10. 2. 2020	THXY26
1855555	4. 3. 2020	THXY26
377777	10. 2. 2020	THXY26

I have a table of products (with a lot more rows and columns) which can be identified by their ProductIDs. These products have multiple single parts, which can be identified by their Serialnumbers. If one of those single parts are exchanged or updated, then a new row with the ProductID, the Serialnumber of the new part and the date (LastModified) when the part was updated or exchanged gets created in the table. So in the above example the product with the ID THXY24 had the part with the serialnumber 1xxxxx replaced on 7. 6. 2020. The row with the old part from 4. 2. 2020 remains in the table eventhough it is no longer relevant. 

My task is to filter the table and to remove the rows of parts which were updated. So in the above example I want to remove the rows corresponding to 178888 and the row corresponding to 1855555 but I am completely stuck. I have tried to implement solutions like this one but I have not gotten far. 

The only relevant part of the serialnumbers are the first two digits (the red ones). These identify the part and the rest give a description of it. There are only a set amount of numbers which could be in the first two digits (numbers like 1, 3, 5, 18, and a few more) so I could try to use them in the filter but I don't know how. 

Any help would be greatly appreciated! Thanks.

[harshnathani]

Hi @Anonymous ,

Create a Table

Table1 =
SUMMARIZE (
    Table,
    Table[Product id],
    "LAST Date", LASTNONBLANKVALUE (
        Table[Last Modified],
        MAX ( Table[Last Modified] )
    ),
    "Serial No", LASTNONBLANKVALUE (
        Table[Last Modified],
        MAX ( Table[Serial No] )
    )
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , Logic to identify, what relaced what is still not clear to me. Can you explain with an example 

There are options, but they might not work for you

https://www.youtube.com/watch?v=rqDdnNxSgHQ

[Anonymous]

Hey @amitchandak, I'm sorry, my question could have been formulated clearer. 

I have the above table. The product with the ID THXY24 has two rows with serialnumbers starting with 1xxxxx. One of them was last modified on 4. 2. 2020 (the old one) and one was last modified on 7. 6. 2020 (the new one). In this case whatever part is identified by the serialnumber 1xxxxx was replaced on 7. 6. 2020. 

I now want to get rid of the row corresponding to the product ID THXY24 with the serialnumber 1xxxxx which was last modified on 4. 2. 2020, as it is currently not in use anymore. 

Similarly the product with the ID THXY26 has two rows with serialnumbers starting with 18xxxxx. One of them was last modified on 10. 2. 2020 (the old one) and one was last modified on 4. 3. 2020 (the new one). In this case whatever part is identified by the serialnumber 18xxxxx was replaced on 4. 3. 2020. I want to get rid of the row corresponding to the product ID THXY26 with the serialnumber 18xxxxx which was last modified on 10. 2. 2020 as it is no longer relevant. 

My problem is that I can't figure out how to filter out the older, irrelevant rows to delete them afterwards.

I hope this made my question clearer.

[amitchandak · Accepted solution]

@Anonymous , I would prefer to create these two columns and old is that can be deleted

Key = right([Serialnumber], len([Serialnumber])-5)

new column =
if([LastModified]= maxx(filter(table , [Key] = earlier([Key]) && [ProductID] = earlier([ProductID])),[LastModified]) ,"Latest","Old")

See if this works for you

[Anonymous]

Once again you've helped me a lot. Thank you!