Forum Discussion

florent-3628's avatar
florent-3628
Frequent Visitor
7 years ago
Solved

Remove duplicate with date condition

Hi,

I'm trying to remove duplicates in a table (column "id")  ; but i want to keep only the most recent .

 

Actually , even if i sort by date before using table.distinct ; the result keep an old date...

 

the table is like this

Version     id     date

v23A02/03/2019
v23B02/04/2019
v24A05/04/2019
v24B06/04/2019
v25A24/05/2019
v25B25/05/2019

 

The actual result after table.distinct :

v23A02/03/2019
v23B02/04/2019

 

The result i want :

v25A24/05/2019
v25B25/05/2019
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    In Edit Queries make sure to filter descending on date. 

     

    Then add 

     

        #"Buffer Table" = Table.Buffer(#"Sorted Rows")
in
    #"Buffer Table"

    to the advanced editor

     

    After that use remove duplicates on ID, it will remember the order and keep v25.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    In Edit Queries make sure to filter descending on date. 

     

    Then add 

     

        #"Buffer Table" = Table.Buffer(#"Sorted Rows")
in
    #"Buffer Table"

    to the advanced editor

     

    After that use remove duplicates on ID, it will remember the order and keep v25.

    • LucienF38's avatar
      LucienF38
      Helper I
      7 years ago

      Hi Anonymous ,

       

      I have the same issue but I want to do it in DAX because I want to keep only the earliest rows from a concatenate create in DAX from 2 different tables... Do you have any idea on how to remove the duplicates but only the latest ones ? 

       

      Thanks :) 

    • LucienF38's avatar
      LucienF38
      Helper I
      7 years ago

      Hi Anonymous ,

       

      I have the same issue but I want to do it in DAX because I want to keep only the earliest rows from a concatenate create in DAX from 2 different tables... Do you have any idea on how to remove the duplicates but only the latest ones ? 

       

      Thanks :)