Help with Identifying the Most Recent Contract

re: "Ideally, this should be foldable, since the source is a SQL Azure DB."

In the attached is a query which is very unlikely to be foldable.

I'm still learning m code so this effort is probably (a) naïve and (b) will take an age!

It currently gives the results you expect, however. 

Here's another M-Code solution. Although I believe an upstream SQL solution would be the most efficient:

Original Data:

let
    Source = Table, //Your original data table
    
    #"Group Account" = Table.Group(Source, "AccountID",{
        {"all", (t)=>
            if List.Contains(t[Status],"Active") 
                then Table.SelectRows(t, each [Status] = "Active")
            else if List.Contains(t[Status],"Future")
                then [a=List.Select(t[StartDate], each _ > Date.From(DateTime.FixedLocalNow())),
                      b=List.Min(a),
                      c=Table.SelectRows(t, each [StartDate]=b)][c]
            else Table.SelectRows(t, each [StartDate] = List.Max(t[StartDate])),
            type table[ContractID=Int64.Type, Status=text, StartDate=date, EndDate=date]}
        }),
        
    #"Expanded all" = Table.ExpandTableColumn(#"Group Account", "all", {"ContractID", "Status", "StartDate", "EndDate"})
in
    #"Expanded all"

Hi DerhakRon,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks SundarRaj, ronrsnfld, p45cal, AlexisOlson, for those valuable insights for this thread.

After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.