Forum Discussion

Dayna's avatar
Dayna
Helper V
4 years ago
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...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
Dayna
Helper V
4 years ago

I'll be using the data differently depending on my dataflow, so ideally I'd like to do this in PowerBI itself.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Dayna  PQ has performance issue which really does not matter for a small dataset. But for large tables, if the server-side transformation/filtering/aggregation is not applied, then PQ will not do it any faster. But if that is your only choice, I am not sure how can you troubleshoot that.