Forum Discussion
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 approach to sort the date in descending order, using table.buffer on the sort, then the next step to remove duplicates on my unique key.
This is fine when I do this on smaller datasets, but on large ones it's just causing the query to crash or not load at all.
Can you assist, please? The datasource is coming from SQL, so I'm not sure if there's something smart I can do in the source step using some SQL or not..
Hopefully the above makes sense,
Many thanks,
Dayna
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
5 Replies
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 grpI'll be using the data differently depending on my dataflow, so ideally I'd like to do this in PowerBI itself.
