How to remove duplicate rows of lesser value

[Shannyn]

Hi All, 

Hoping you can help.

I am trying to remove rows from a file where there is a duplicate Product ID, but I wish to remove the row that contains a 'Sales' amount of the lesser value.

Any suggestions would be greatly appreciated

Please see snip below

Cheers!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[v-yueyunzh-msft · Accepted solution]

Hi , @Shannyn 

Based on your description, you want to keep the record of the largest [Sales] of the duplicate [Product_ID].Right?

It will be easier in Power Query Editor, Here are the steps you can refer to :

(1)This is my test data :

(2)We can click "Insert Step After" to create a step in the query and enter M language:

= Table.Group(#"Changed Type" , "Product_ID" , {"test",(x)=> Table.Sort(x,{"Sales",1}){0}   }    )

The #"Changed Type"  is your last step name.The result is as follows:

(3)Then we can expand the "Record" and we will meet your need:

The result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Shannyn , I think you can manage that in a  measure

sumx(Values(Table[Product]), calculate(Max(Table[Value])) )

or create a flag measure

if([Value] = maxx(filter(Table, [Product] =earlier([Product]) ), [Value],1,0)

filter for 1

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8