cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyabes
Helper I
Helper I

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

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?

 

2018-12-09_9-09-24.png

 

Thanks in advance!

David

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

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.

View solution in original post

Hi @dyabes

 

Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
arunhariharan
Frequent Visitor

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
Super User
Super User

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.

View solution in original post

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

 

2018-12-09_10-36-51.png

Hi @dyabes

 

Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

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.

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!