Remove duplicates based on values

[QuasemS]

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!

[Beckham · Accepted solution]

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.

 

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.

[augustindelaf]

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

[augustindelaf]

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

[Anonymous]

Thanks @augustindelaf the YouTube link you posted helped me emensely 

[augustindelaf]

You are Welcome @Anonymous !

[ruthpozuelo]

Many thanks for the mention! /Ruth

[Beckham]

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

[pkumar]

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

[PBI_KLo]

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.

[pkumar]

How do you expand all the rows?