Help with Identifying the Most Recent Contract

[DerhakRon]

I am trying to identify the most recent contract associated to an account.  I am able to do this in DAX, but seeing as my source Contract table is 24 Million Rows, this is not too efficient. I'm trying to do this in PQ to reduce the row count (total result would be around 9 Million Accounts) and not getting to the proper result.  I think I need to group by Account and then apply the logic (below) to the resulting subtable.  

Here is an example of my data

AccountID	ContractID	Status	StartDate	EndDate
1	1	Future	1/1/2026	12/31/2027
1	2	Active	1/1/2025	12/31/2025
1	3	Past	1/1/2024	12/31/2024
2	4	Active	1/1/2025	12/31/2025
2	5	Past	1/1/2024	12/31/2024
3	6	Future	1/1/2026	12/31/2027
3	7	Active	1/1/2025	12/31/2025
4	8	Future	1/1/2026	12/31/2027
4	9	Past	1/1/2024	12/31/2024
5	10	Future	1/1/2027	12/31/2027
5	11	Future	1/1/2026	12/31/2026
6	12	Active	1/1/2025	12/31/2025
7	13	Past	1/1/2024	12/31/2024
7	14	Past	1/1/2023	12/31/2023

I'm trying to get a final table that follows this bit of logic

1. If the Account has an Active Contract, keep that contract and remove all others
2. If the Account only has Past Contracts, keep the most recent one
3. If the Account only has Future Contracts, keep the one that starts next
4. If the Account has Future and Past Contracts, keep the one the starts next

Ultimately, the final table should be

AccountID	ContractID	StartDate	EndDate
1	2	1/1/2025	12/31/2025
2	4	1/1/2025	12/31/2025
3	7	1/1/2025	12/31/2025
4	8	1/1/2026	12/31/2027
5	11	1/1/2026	12/31/2027
6	12	1/1/2025	12/31/2025
7	13	1/1/2024	12/31/2024

Ideally, this should be foldable, since the source is a SQL Azure DB.  However, I understand if that is not doable, as some of the functions that may be needed to get this result may not be foldable.  Any help, pointers or links to similar posts or tutorials is appreciated.

[Poojara_D12]

Hi @DerhakRon 

To efficiently filter your 24-million-row Contract table in Power Query (especially with a foldable query against Azure SQL), you can use a grouping and conditional filtering approach that mimics your DAX logic. The key is to group by AccountID and then apply conditional logic within each group to retain only the contract that fits your priority criteria: (1) if there's an Active contract, keep the most recent Active one; (2) if only Past contracts, keep the latest by StartDate; (3) if only Future contracts, keep the one with the soonest upcoming StartDate; (4) if both Future and Past exist but no Active, still pick the soonest Future one. In Power Query (M), this typically involves using Table.Group to nest contract records per account, followed by a custom column using Table.SelectRows and Table.Sort logic to apply your filtering rules within each subgroup. While Power Query itself may not support native SQL folding with this entire logic chain, you can try using Table.Group with as few transformations as possible inside the sub-tables, or even consider building the logic as a SQL view in Azure and using Power BI to connect to that filtered view instead. This offloads the filtering to the server and ensures scalability. If performance is still critical, you might consider creating a SQL stored procedure or computed view that encapsulates this logic using ROW_NUMBER() partitioned by AccountID with CASE statements prioritizing status. That would yield a much leaner dataset to import or query directly into Power BI.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @DerhakRon,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @DerhakRon,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @DerhakRon,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[SundarRaj]

Hi @DerhakRon ,
Here's another M-Code solution. I'll leave the M-Code and the image of the output below for your reference. Thanks

Here's the M-Code:
let
Source = #table(
{"AccountID", "ContractID", "Status", "StartDate", "EndDate"},
{
{1, 1, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{1, 2, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{1, 3, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{2, 4, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{2, 5, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{3, 6, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{3, 7, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{4, 8, "Future", #date(2026, 1, 1), #date(2027, 12, 31)},
{4, 9, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{5,10, "Future", #date(2027, 1, 1), #date(2027, 12, 31)},
{5,11, "Future", #date(2026, 1, 1), #date(2026, 12, 31)},
{6,12, "Active", #date(2025, 1, 1), #date(2025, 12, 31)},
{7,13, "Past", #date(2024, 1, 1), #date(2024, 12, 31)},
{7,14, "Past", #date(2023, 1, 1), #date(2023, 12, 31)}
}
),
ColNames = List.Skip ( Table.ColumnNames ( Source ) , 1 ),
#"Grouped Rows" = Table.Group(Source, {"AccountID"}, {{"AllTable", each _, type table [AccountID=number, ContractID=number, Status=text, StartDate=date, EndDate=date]}}),
#"Group Fx" = Table.TransformColumns ( #"Grouped Rows" , {"AllTable" , each if List.Contains ( _[Status] , "Active" ) then Table.SelectRows ( _ , each [Status] = "Active" ) else if List.ContainsAll ( {"Future"} , _[Status] ) then Table.LastN( _ , 1 ) else Table.FirstN ( _ , 1 ) } ),
#"Expanded AllTable" = Table.ExpandTableColumn(#"Group Fx", "AllTable", ColNames, ColNames)
in
#"Expanded AllTable"

[ronrsnfld]

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"

[p45cal]

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. 

[AlexisOlson]

I would do this in SQL, either upstream in Azure or as a custom query in Power Query. Something like this:

WITH ranked AS (
    SELECT
        AccountID,
        ContractID,
        StartDate,
        EndDate,
        Status,
        ROW_NUMBER() OVER (
            PARTITION BY AccountID
            ORDER BY
                CASE Status
                    WHEN 'Active' THEN 1
                    WHEN 'Future' THEN 2
                    ELSE               3
                END,
                /* Tie-breakers */
                CASE WHEN Status = 'Past'   THEN StartDate END DESC, -- last Past
                CASE WHEN Status = 'Future' THEN StartDate END ASC   -- next Future
        ) AS rn
    FROM YourSourceTableHere
)
SELECT
    AccountID, ContractID, Status, StartDate, EndDate
FROM ranked
WHERE rn = 1;