Evaluation based on 2 most recent entries per client

[tnemeth]

I'm having the darndest time trying to figure out how to even search for this or phrase the problem I'm facing.

My data looks like the sample below. This is just 8 of 800 entries, and I'm expected to have tens of thousands of entries in the next year.

What I need to do is evaluate each Client# based on the results of their most recent, and second most recent completion date.

FYIs:

• The 'Follow Up' column is my first attempt at this, but it's only evaluating each row as opposed to taking two rows into account.
• Very few people have 2 results right now, so it's important that whatever is done will evaluate clients with one result if they only have one result

What I tried so far:

• Not much, because I literally can't see a way forward with what I currently know
• I did try changing the format of the data I have by grouping (and expanding the group) in order to add an index of each Client# ... but then I couldn't really see how this was going to help me. See below for this attempt.
• 
• I also played around with the idea of pivoting information around, and I feel like it gets me 90% of the way there EXCEPT I don't know how to handle/manipulate it from here when there are TWO results that say 'continue' (as an example). This works great if somebody could only get one result once. See below for this attempt.
• 
  • The main failure here (to me) was that each client has 2 rows. I figured that was happening because of the Follow Up column... so I removed that column and wound up with the below errors... and I understand the error: It can't put both dates into one pivoted column...
  • 

If it's helpful, here is a 'sketch' of the finished product I'm aiming for:

1 row per client saying what has to happen and at what follow up date. For example:

• If the client's most recent result is 'Continue' then there should be an action of "Reassess Client" and a follow up date of"[Completion Date] + 365 days"
• If a client's most recent result is 'Disenroll' (and there the second most recent result is NOT 'Disenroll') then there should be an action of "Reassess Client OR Disenroll by..." and a follow up date of "[Completion Date] + 60 days"
• If a client's most recent result is 'Disenroll' (and there the second most recent result is also 'Disenroll') then there should be an action of "Terminate Client Immediately" and a follow up date of "[Today]"

Any ideas? I think I just need to get my data into a different format... My latest hunch is if I could just add an index to each result (per client) so I can identify the most recent, and second most recent 

TL;DR How can I get the screenshots above to get an index added like the below to the left so I can pivot to the below on the right and do all my logic?

[AlienSx · Accepted solution]

Hello, @tnemeth i give you an idea but you need to work on it

// this groups data by client and collects info about last 2 results and last date
    g = Table.Group(
        your_table, "Client", 
        {{"status", 
        (x) => 
            [last2 = Table.FirstN(Table.Sort(x, {"Completion Date", Order.Descending}), 2),
            r = [s1 = List.First(last2[Result]), s2 = last2[Result]{1}?, d = List.First(last2[Completion Date])]][r]
        }}
    ),
// this transforms "status" column into record with action and follow up fields. You need to add more if/then/else
    txf = Table.TransformColumns(
        g, 
        {"status", each 
            if [s1] = "Continue" then [Action = "Reassess Client", FollowUp = Date.AddDays([d], 365)] else 
            if [s1] = "Disenroll" and [s2] = "Disenroll" then 
                [Action = "Terminate Client Immediately", FollowUp = Date.From(DateTime.FixedLocalNow())]
            else [Action = "TBD", FollowUp = "TBD"]
        }
    ),
// this expands status record field into 2 new columns
    expand = Table.ExpandRecordColumn(txf, "status", {"Action", "FollowUp"})

to feed the code with all the cases. Modify txf step according to your needs. I processed 2 cases. You'll need to complete this job. 

[tnemeth]

OK this is a little weird and roundabout but I saw what your code was trying to do and I couldn't get it to work completely. But it seemed close and then I googled one other thing to try which led me to this thread which then got me to the reformatted data I was looking for.

So thank you! There was something that you wrote that made me better understand what I was missing and a different question I should be trying to ask.

If anybody else has any other cleaner ideas then please let me know, but here is my new code:

    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Client", Order.Ascending},{"Completion Date", Order.Descending}}),
    #"BuffBuff" = Table.Buffer(#"Sorted Rows"),
    T = Table.Buffer(Table.AddIndexColumn(#"BuffBuff", "Order", 0, 1, Int64.Type)),
    AddIndex = Table.FromRecords(
        List.Generate(
            () => [Order = 0, Index = 1],
            each [Order] < Table.RowCount(T),
            each [
                    Order = [Order] + 1,
                    Index = if T{Order}[Client] = T{[Order]}[Client] then [Index] + 1 else 1
                ]
        ),
        type table [Order = Int64.Type, Index = Int64.Type]
    ),
    #"Merged Queries7" = Table.NestedJoin(T, {"Order"}, AddIndex, {"Order"}, "Index", JoinKind.LeftOuter),
    #"Expanded NewID" = Table.ExpandTableColumn(#"Merged Queries7", "Index", {"Index"}, {"Index"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded NewID", each [Index] <= 2),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Order"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US"),{"Index", "Result"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Recency Result"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Recency Result"]), "Recency Result", "Completion Date")
in
    #"Pivoted Column"

 (The ending bit, anyway)

And my result looks almost exactly like the paint picture i made, lol

Thanks again!!