cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
QuasemS Frequent Visitor
Frequent Visitor

Remove duplicates based on values

Hi,

 

I'm trying to remove rows based on duplicate values in a column. I know how to use the Query Editor to do this. However, the duplicate values in that column has different values corresponding to it in the table. I only want to keep the record with the highest value in the other columns, and get rid of the duplicates with have lower values.

 

When I do the regular remove duplicates step, it always seems to keep the lowest value. I want to do the exact opposite. Anyone know if there is some flexibility with this option where I can achieve this?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Beckham Regular Visitor
Regular Visitor

Re: Remove duplicates based on values

I've had this problem before. I'm using datesThe way I found around it was 4 steps:

 

Step #1. Use group by to find the max value. Add all rows to the bottom.

 1.PNG

Step #2. Expand all the rows. Basically this adds a new column with the max values down the side of the table

 

Step #3. Add a custom column like this:  if date_applied = Max Date then true else false

 

Step #4. Filter down to just true. 

 

Hope this helps.

View solution in original post

10 REPLIES 10
Highlighted
Beckham Regular Visitor
Regular Visitor

Re: Remove duplicates based on values

I've had this problem before. I'm using datesThe way I found around it was 4 steps:

 

Step #1. Use group by to find the max value. Add all rows to the bottom.

 1.PNG

Step #2. Expand all the rows. Basically this adds a new column with the max values down the side of the table

 

Step #3. Add a custom column like this:  if date_applied = Max Date then true else false

 

Step #4. Filter down to just true. 

 

Hope this helps.

View solution in original post

pkumar Frequent Visitor
Frequent Visitor

Re: Remove duplicates based on values

How do you expand all the rows?

pkumar Frequent Visitor
Frequent Visitor

Re: Remove duplicates based on values

Is this the only way to do this? Looks like my data might be too large so it's not loading.

PBI_KLo Frequent Visitor
Frequent Visitor

Re: Remove duplicates based on values

Even if you didn't add the extra test, the grouping would by default eliminate the non-grouped subset. At least that's what I'm seeing with my dataset. I have a group of users who purchased items at different dates. I wanted to look for their earliest order date, so I grouped a "Min" on the order date and was left with that.

Re: Remove duplicates based on values

very interesting way, thank you @Beckham

Helped me a lot.

My problem seemed very specific but hopefully some other people like you managed to solve it.

that's great. 

thanks

 

Re: Remove duplicates based on values

Hi all,

 

I know a better way (the easiest one, for sure !) which is explained here in a video made by a clever Power BI specialist known as Curbal :

https://www.youtube.com/watch?v=rqDdnNxSgHQ&index=22&list=PLDz00l_jz6zzttb28XH8GHZNL6vvpBlkQ

Beckham Regular Visitor
Regular Visitor

Re: Remove duplicates based on values

That's GREAT!!! Thanks for the tip!

ruthpozuelo
Advisor

Re: Remove duplicates based on values

Many thanks for the mention! /Ruth
xabit Frequent Visitor
Frequent Visitor

Re: Remove duplicates based on values

Thanks @augustindelaf the YouTube link you posted helped me emensely 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,445)