Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ilyaadna
Regular Visitor

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!! 

 

ilyaadna_0-1675696070859.png

 

3 REPLIES 3
Anonymous
Not applicable

Hi  @ilyaadna ,

 

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

vyangliumsft_0-1675756955705.png

 

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

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

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.