Search code examples
pivot-tablepowerquery

Filter the 1st payment of each protocol out of 500k rows of payment record

I have export a report with 500k rows of transactions of each item. I need each protocol's 1st/oldest payment date. I used pivot table to get 506 protocols with each transaction number and date. But I just want each protocols first/oldest traction

I tried power query to cleaned up and then pivot it. But still cannot filer the 1st payment per protocol. As each transaction contains multiple activities with different date

Solution

  • In PowerPivot add new column with this expression:

    = 
  var thisP = [Protocol ID]
  var firstD = CALCULATE(MIN('Transaction'[Date]), ALL('Transaction'), 'Transaction'[Protocol ID] = thisP) 
  var lastD = CALCULATE(MAX('Transaction'[Date]), ALL('Transaction'), 'Transaction'[Protocol ID] = thisP)
  RETURN SWITCH([Date], firstD, "First", lastD, "Last")

    This column with now have "First" or "Last" or "BLANK()" which you can then use for your PivotTable filter.

    • Change 'Transaction' to the name of your table.
    • Change [Protocol ID] to the column of your "protocol ID".
    • Change [Date] to the column of your transaction date.

    This can also be done in PowerQuery too, if you'd like that then update your question with a sample of your table.