Dax or Power Query formula

[Anonymous]

Hi All

Below is a snapshot of some data I am trying to clean. 

The id will be the same with the attempts 1-5 and the worktime (secs) is actually a running total. So, I need to only report on the worktime of attempt 5 (as that is the actual amount of time it took) but need the other 4 attempts in there for performance evalution. 

I need to be able to say: if attempt nr is > 1 and the ID is the same then only show the worktime (secs) of the row with the greatest attempt nr. 

I am just not sure on how to say this in Dax or Power Query. Any ideas on how I may be able to do this?

Cheers

Karen

[PC2790 · Accepted solution]

Not sure if this is the most efficient way, but I was able to address your requirement using Power Query and DAX.

Uploading a sample file here for you to access

[Anonymous]

Thanks @PC2790. I will have a look at this today. Thank you. 

[PC2790]

Hey,

Did it work for you or you are looking for something else?

[YukiK]

In Power Query, you could add a conditional column that flags 1 if "Attempt" is 5, and 0 otherwise.

If this helped, please select as the solution!

[Anonymous]

Thanks @YukiK. Unfortunately that won't work. I need something that says, if the id is the same check how many attempts are in the attempt column and only add the latest attempt (so that could be 2, 3, 4 or 5) to all 1 attempts. Just not sure how to do it. 🤔