Rows - Maximum Date

Hello jerryr,

You can also do this in the PowerQuery query editor using Table.Group function:

Here my simulation script for your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc1LCsAwCATQu7gO+IuYbdtjBO9/jdpEUpiNvkHnhAsaKCMxConlwKIQbUNu+YB2W3B/JcJMgXU9QIqZAvex4KlT9P9wiHgB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DepartmentID"}, {{"LastDate", each List.Max([Date]), type nullable date}, {"TransactionID", each List.Max([TransactionID]), type nullable text}})
in
    #"Grouped Rows"

✅ If this response resolved your issue, please mark it as correct to assist other members of the community.

Here's how you can lookup the record with max date, assuming there is only 1 row per date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each _{[Date=List.Max([Date])]}, type [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandRecordColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields

alternatively, this will get you all rows with the max date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each Table.SelectRows(_, (x)=> x[Date]=List.Max([Date])), type table [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandTableColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields

Hey,

This method checks against the max date for that apartment ID and then for the largest Transaction ID. If there are multiple records for that date for that departmentID. If you remove the filter_TransactionID, you get filtering only on date.

let
    // replace YOURDATA with your source
    Source = YOURDATA,

    // select rows that match the Max date for that DepartmentID
    filter_maxDate = Table.SelectRows(
        Source, 
        each ([Date] = List.Max(Table.SelectRows(Source, (Row) => Row[DepartmentID] = _[DepartmentID])[Date]))
        ),

    // select rows that match the Max transactionID for the DepartmentID
    filter_maxTransactionID = Table.SelectRows(
        filter_maxDate, 
        each ([TransactionID] = List.Max(Table.SelectRows(filter_maxDate, (Row) => Row[DepartmentID] = _[DepartmentID])[TransactionID]))
        )
        
in
    filter_maxTransactionID

Hope this helps!
If any of these solutions helps you, please consider to give kudo's and accept an answer so other users with similar problems can find the answer quickly!

Hi jerryr,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by community members for your issue worked for you or let us know if you need any further assistance?

Chewdata, m_dekorte, PhilipTreacy& Zanqueta, thanks for your prompt response

Your feedback is important to us, Looking forward to your response

Thanks,

Prashanth

Hi @jerryr,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by community members for your issue worked for you or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response

Thanks,

Prashanth