Forum Discussion
Solved
How to get latest record using Power Query on a large dataset?
Hello, I have quite a large dataset and I need to filter out duplicate records and always bring back the latest record as per the field 'modifieddate'. Looking on the forums, I've seen the ap...
Dayna if you are getting it from SQL, you can do this easily on the SQL side and would also be more performant, quicker.
Example
declare @t1 as table (date date, record int, grp int) insert into @t1 select * from (values('2021-1-1',1,1),('2021-1-2',2,1),('2021-1-3',3,1), ('2021-1-1',1,2),('2021-1-2',2,2),('2021-1-3',3,2) ) t (a,b,c) select grp,record,date as date from @t1 a where a.date in (select max(b.date) from @t1 b group by grp) order by grp
Dayna if you are getting it from SQL, you can do this easily on the SQL side and would also be more performant, quicker.
Example
declare @t1 as table (date date, record int, grp int)
insert into @t1
select * from
(values('2021-1-1',1,1),('2021-1-2',2,1),('2021-1-3',3,1),
('2021-1-1',1,2),('2021-1-2',2,2),('2021-1-3',3,2) ) t (a,b,c)
select grp,record,date as date
from @t1 a
where a.date in (select max(b.date) from @t1 b group by grp)
order by grp
