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

Question

I would like to remove duplicate values from the above table based on conditions " 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.

Answer 1

To remove duplicate values based on multiple columns with a condition in POWER BI.

You need to go through some steps one by one.

1. At first, you need to import your data loaded like below, and just accept the changes made under Changed Type:

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

3. After adding column then you need to customize and 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. Then click OK, and make sure that you're getting this:

5. You need to follow the steps 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:

After doing the process you will get your solutions...