Forum Discussion

Mahi1989's avatar
Mahi1989
Frequent Visitor
8 years ago
Solved

Remove duplicates values based on multiple column with a condition in query editor

Hi All, 

I am new to power bi and would require your help to sort out below issue which i am facing.

basically i am taking three columns into consideration as below

 

 

 

 


Question: I would like to remove duplicate values from above table based on conditon "
Equal value for "Time" ,"ID" and Absolute difference in "Time spent" is lower or equal than 1"
as you can see in the image Rows highlighted falls in this category.

 

would like to get these below rows removed based upon condition.

 

 

 

And final solution should look like this:

 

 

I am able to perform this in excel by making us of a fourth column with formulae "=IF(AND(A3=A2,B3=B2,ABS(F3-F2)<1),"problem",0) " and then filtering out the rows marked as probelm.  Please help!!

 

Regards

Mahi

 

 

 

 

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    8 years ago

    Hi@Mahi1989

    You use Merge Queries function in Edit Queries.

    You can try to do it follow my steps:

    Step 1:

    Duplicate the data table as below

    Step 2:

    Add index column for two table

    Basic table start from 1 and increment is  1

    Duplicate table start from 0 and increment is  1

    Step 3:

    Merge two table

    Step 4:

    Expand the table and apply &close

    Step 5:

    Add column

    Column = IF(Table4[Time]=Table4[Added Index.Time]&&Table4[ID]=Table4[Added Index.ID]&&ABS(Table4[Time Spent]-Table4[Added Index.Time Spent])<1,1,2)

    Step 6:

    Filter the table

     

    Here is demo ,please try it.

    .PBIX https://www.dropbox.com/s/05w8ln994lz0kwu/Remove%20duplicates%20values%20based%20on%20multiple%20column%20with%20a%20condition%20in%20query%20editor.pbix?dl=0

    EXCEL  https://www.dropbox.com/s/gle4648a4c81kro/example.xlsx?dl=0

     

     

     

    Best Regards,

    Lin

    • Mahi1989's avatar
      Mahi1989
      Frequent Visitor
      8 years ago

      Hi Lin,

       

      Thanks for saving my day it worked, cheers mate :)

       

      Regards

      Sumit

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago
    If you just want to exclude the data from your model entirely it is best to do it I. Power query before you load the data to your model. I’m on my phone so having you the walk trough of how to do this is difficult because I do t remember all the menu commands off the top of my head. But basically highlight those columns and under remove rows hit remove duplicates. You may also want to duplicate the query and keep only duplicates and return a problem table but not required if you don’t want to look at it.

    You can do it in Dax but don’t recommend this unless you need all the data for other calculations since the Dax gets messy and requires the use of EARLIER which while easy is conceptually challenging. It’s hard in Dax since each cell is independently calculated and you can lookup the value in another cell.