PowerQuery: How to return MAX DATE only if MAX DATE is unique

[Sindrin]

Hi Guys,

I have 3 columns

1 - Unique ID
2 - Submission Date
3 - Status

I managed to pull the max date of the unique ID using :

GROUP BY :
Unique ID

NEW COLUMN:
Col Name: MaxDate, Operation: Max, Column Submt Date

THEN:
Expand All, each Max Date = Submission Date.

However I have instances where for a particular Unique ID there are multiple occurrence of the exact same date.

Is there a way to pull only a max date where the max date occurs once (e.g. not a max date deriving of the same exact dates)?

So final output would be:

1 - Unique list of Unique IDs
2 - Max Date (deriving from unique max dates, not from multiple exact same dates)
3 - Status related to the Max Date

Thank you in advance!

Prinz

 

[alansidman]

Some sample data to test would help to solve this issue. Use the XL2BB function to post your data.

 

[Sindrin]

Some sample data to test would help to solve this issue. Use the XL2BB function to post your data.

Hi Alan,

Unable to post the file as the file is related to bank transactions.

However the format of the file follows the below :

unique ID Submission date Status
BBH123 01/04/2021 Accepted
BBH456 01/04/2021 Rejected
BBH789 28/03/2021 Accepted
BBH456 28/03/2021 Accepted
BBH123 01/04/2021 Accepted
BBH789 28/03/2021 Accepted

The ideal output would be:

unique ID Submission date Status
BBH456 01/04/2021 Rejected
etc...

Plus any other unique ids with a unique max date.

The below unique ids will be excluded as there is no unique max date:

BBH123
BBH789

Thank you!