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

[Mahi1989]

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

[v-lili6-msft · Accepted solution]

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%20col...

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

Best Regards,

Lin

[Mahi1989]

Hi Lin,

Thanks for saving my day it worked, cheers mate 🙂

Regards

Sumit

[Seward12533]

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.