Filter rows by max date keeping ID

[neilmc]

Hello I have the data below

Ideally I would like to select for each PerID the containing the latest date.  I've tried groupby in query editor (groupby perID then select Max date and all rows, however when I expand the table to get the DBID field I end up with duplicates

Any pointers gratefully recieved

Cheers

Neil

PerID	DBID	StartDate
PER10007	33918908
PER10007	33928550	02/08/2010 23:00
PER10022	33900446	09/05/2017 00:00
PER10022	33918909
PER10022	33923009
PER10022	33928551	06/11/2007 00:00
PER10029	33873682	06/07/2020 23:00
PER10029	33897224	01/02/2023 00:00
PER10029	33900447	03/10/2018 00:00
PER10029	33900448	08/10/2018 00:00
PER10029	33900449	18/10/2018 00:00
PER10029	33900450	01/11/2018 00:00
PER10029	33900451	05/04/2019 00:00
PER10029	33900452	22/07/2019 00:00
PER10029	33900453	13/08/2019 00:00
PER10029	33910860	15/09/2020 00:00
PER10029	33912610	06/02/2023 00:00
PER10029	33912611	12/04/2023 00:00
PER10029	33917448	12/04/2023 00:00

[v-tangjie-msft · Accepted solution]

Hi @neilmc ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table 2 = 
var _a=ADDCOLUMNS('Table',"max", CALCULATE(MAX('Table'[StartDate]),FILTER('Table','Table'[PerID]=EARLIER('Table'[PerID]))))
var _b= FILTER(_a,[StartDate]=[max]) 
return SELECTCOLUMNS(_b,"PerID",[PerID],"DBID",[DBID],"StartDate",[StartDate])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[neilmc]

Hello - I think I was being I bit of an idiot!  It looks like the grouping method worked in power query but the issue was that I had duplicate person ids with exactly the same start date

[Ahmedx]

can you show the result you expect