I have a dataset like this, which shows who approved a certain report. In my organisation, in some cases(eg.Report ID AB3), reports get rejected by a separate approver(which is out of the scope of this question) and the report is sent back to be edited and resubmitted and there might be a different approver.
| Approver | Report ID | Action |Date |
| | AB3 | Report Archived |30/12/20|
| John | AB3 | Report approved |27/12/20|
| | AB3 | Report Submitted |26/12/20|
| | AB3 | Report Generated |25/12/20|
| | AB3 | Report Resubmitted|24/12/20|
| | AB3 | Report Rejected |23/12/20|
| Alan | AB3 | Report approved |22/12/20|
| | AB3 | Report Submitted |21/12/20|
| | AB3 | Report Generated |20/12/20|
| | AB4 | Report archived |25/12/20|
| Tommy | AB4 | Report approved |21/12/20|
| | AB4 | Report Submitted |20/12/20|
| | AB4 | Report Generated |18/12/20|
However I would like to create a new column with the latest approver's name(by date) for all report IDs by that approver. It should look like this for the end result. In essence I would like to take the latest(date) with the Approver being a non-null value.
|Latest Approver | Approver | Report ID | Action |Date |
| John | | AB3 | Report archived |30/12/20|
| John | John | AB3 | Report approved |27/12/20|
| John | | AB3 | Report Submitted |26/12/20|
| John | | AB3 | Report Generated |25/12/20|
| John | | AB3 | Report Resubmitted |24/12/20|
| John | | AB3 | Report Rejected |23/12/20|
| John | Alan | AB3 | Report approved |22/12/20|
| John | | AB3 | Report Submitted |21/12/20|
| John | | AB3 | Report Generated |20/12/20|
| Tommy | | AB4 | Report archived |25/12/20|
| Tommy | Tommy | AB4 | Report approved |21/12/20|
| Tommy | | AB4 | Report Submitted |20/12/20|
| Tommy | | AB4 | Report Generated |18/12/20|
Apologies for the messy table. Thanks in advance!