Remove duplicate and keep row based on a column with the lowest value

[dyabes]

I'm tyring to remove duplicate on the ID column but would like to keep the row based on the date with the lowest value. Basically, keep the ID with the earliest instance. I tried the Sort -> Table.Buffer -> Remove Duplicate approach but that process is incredibly slow most likely because the dataset is an append of multiple large CSV files.

Are there other approaches that are more efficient?

Thanks in advance!

David

[AlexisOlson · Accepted solution]

I'd suggest doing a Group By in the query editor. Group by ID and use Min as the aggregation type for the Date column.

[Anonymous]

Hi Alexis

I am also facing similar issue: I have three columns : Supplier Name, Status and Points. there is duplicate value in Supplier and unique value in Status and Points. I need to display the Supplier with lowest points and display corresponding status. Basically need to remove duplicate suppliers keeping the lowest points record

Regards

Arun

[AlexisOlson · Accepted solution]

I'd suggest doing a Group By in the query editor. Group by ID and use Min as the aggregation type for the Date column.

[dyabes]

Thank you. I think I should have provided the complete dataset I'm wokring on. I also need to keep the corresponding row values from other columns

[v-cherch-msft · Accepted solution]

Hi @dyabes

Attached the sample file for your reference.

Regards,

Cherie

[Anonymous]

Hi Dear

You need to use Group By.

Select Group By -> Select ID column as Group BY  and then in Operation select Min and in Column Select Date.
You will get your result.

[AlexisOlson]

You can do what I suggested an then merge the extra column(s) back in after.