在查询编辑器Power BI中基于条件删除基于多列的重复值 [英] Remove duplicates values based on multiple column with a condition in query editor Power BI

问题描述

我是Power Bi的新手，需要您的帮助来解决我所面临的问题。

基本上，我正在考虑以下三列：

问题： 我想根据条件
Time的相等值从上表中删除重复的值， ID和花费时间中的绝对差值小于或等于1
，如您在图像中看到的那样，突出显示的行属于此类别。

我想根据条件删除下面的这些行。

问题：我想根据条件从上表中删除重复的值
Time等于值， ID和花费时间中的绝对差小于或等于1
，如您所见e image高亮显示的行属于此类别。

我希望根据情况删除下面的行。

我能够通过使我们进入具有公式 = IF（AND（A3 = A2，B3 = B2，ABS（F3-F2）< 1），问题，0）的第四列来在excel中执行此操作，然后过滤掉标记为probelm的行。

问候

Mahi

解决方案

我敢打赌@Alexis Olson的建议效果很好，但是由于您特别提到了查询编辑器，因此我将按照以下方式进行操作在那里：

---

1. 已像下面那样加载了数据，并且只接受在下面进行的更改更改的类型：

3. 选择添加列，单击自定义列，然后在出现的对话框 Table.AddColumn（＃ Added Index， Custom，每个＃添加的索引 [花费的时间] {[索引]}-＃添加的索引 [花费的时间{{索引] -1}）：

4. 单击确定，并确保您已获得此提示：

5. 我认为这一步有些奇怪，但是您必须在该列中单击表：

7. 现在，您可以单击自定义列中的下拉菜单，选择数字过滤器，然后选择不等于

8. 并插入0，或从下拉菜单中选择0，对话框中的下拉菜单：

9. 就是这样，您所需的数字现在应被过滤掉：

但是请注意，此过程的成本很高，因为您丢失了第一个索引中第一步的结果。如果可以使用其余的内容，我可以看看是否也可以修复最后的那一部分。

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

解决方案

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.

这篇关于在查询编辑器Power BI中基于条件删除基于多列的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！