Forum Discussion

Shannyn's avatar
Shannyn
New Member
3 years ago
Solved

How to remove duplicate rows of lesser value

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!

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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