Forum Discussion
Filter rows by max date keeping ID
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 |
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.
3 Replies
can you show the result you expect
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
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.
