Find the last transaction using multiple fields RRS feed

  • Question

  • I have about 800k rows of data, with 9 columns.  I need to find the last transaction based on three fields date, id number and code.  Below are the fields in order.

    Date, B.Type, Code, app amt, bill amt, paid amt, rej, ID number, EOM

    is there come kind of m code i can use in power query.  

    I have tried other options, without using power query like count formula and macro.  gave out of memory error. did not work.

    Friday, July 20, 2018 6:34 PM


  • Hi,

    You can add a couple of steps to your existing M code to get the desired result:

    let ...
    PreviousStepName = …,
    GroupedRows = Table.Group(PreviousStepName, {"Code", "ID number"}, {{"Table", each Table.Max(_, "Date"), type table}}),
    LastTransactions = Table.FromRecords(GroupedRows[Table])

    • Proposed as answer by Lz._ Saturday, July 21, 2018 3:19 PM
    • Marked as answer by Imke FeldmannMVP Sunday, July 29, 2018 7:55 AM
    Saturday, July 21, 2018 2:15 PM