Forum Discussion

ilyaadna's avatar
ilyaadna
Regular Visitor
3 years ago

Filter Duplicate rows based on a value in the column

I am new to POWERBI and thanks in advance for everyone's help. Here is my problem. I am trying to remove duplicates based on Scrap_Qty value. If Scrap_Qty=1, it shoud keep that row and filter any other row that has same serial_No but Scrap_Qty=0

 

Here is an example. For HC3078-05, It should keep the top row since Qty_Scrap=1 and then it should compare this serial number with all the other rows and filter out HC3078-05 with Qty_Scrap = 0 (last row in this case).  

 

If i can do that at "transform data" stage, that will be awesome otherwise even a DAX function will work great. Appreciate the help on this!! 

 

 

3 Replies

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

    ilyaadna , Try a new column like

    Flag = 

    var _cnt = countx(FIlter(Table, Table[Serial No] =earlier(Table[Serial No]) , Table[Serial No])

    return

    Switch(True() ,

    Scrap_Qty in {0,1} , 1,

    isblank(_cnt ) ,1 ,

    _cnt=1, 1,

    0)

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  ilyaadna ,

     

    In Power Query – Select the yellow label – select only the box that is 1.

     

    Best Regards,

    Liu Yang

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

    • ilyaadna's avatar
      ilyaadna
      Regular Visitor
      3 years ago

      Thanks for the input Liu. What you are suggesting will filter out all the Qty_Scrap=0. However, what i want to do is to keep all the ScrapQty=1 and filter out only those ScrapQty=0 rows for which there is a serial_no duplicate with Scrap_Qty=1. Again, appreciate your help