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

Question

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.

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

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.

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

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

Answer 1

I bet the suggestion from @Alexis Olson works just fine, but since you specifically mentioned the Query Editor, here's how I would do it there:

---

1. Have your data loaded like below, and just accept the changes made under Changed Type:

Don't worry about the other steps under the Query Settings. We'll get to that eventually.

2. Select Add Column and click Index Column, so that you get this:

3. Select Add Column, click Custom Column and insert this little formula in the appearing dialog box Table.AddColumn(#"Added Index", "Custom", each #"Added Index"[Time Spent]{[Index]}-#"Added Index"[Time Spent]{[Index]-1}):

4. Click OK, and make sure that you're getting this:

5. I think this step is a little weird, but you'll have to click 'Table' there in the column:

6. You will get an Error message in the first row, but you can remove that by right-clicking that column, and clicking Remove Errors:

7. Now you can click the drop-down menu in the Custom Column, select Number Filter and Does Not Equal

8. And insert 0, or select 0 from the drop-down menu in the dialog box:

9. This is it, your required numbers should now be filtered away:

Note, however, that this procedure comes at a cost since you're losing the first value due to the first step in the indexing. If the rest of this is something you can use, I can see if we can fix that last little part as well.