为什么对上一行中的值的引用会引发此 Expression.Error? [英] Why does a reference to a value in previous row throw this Expression.Error?

问题描述

我正在尝试使用 Powerquery 在 Excel 中执行操作.

I am trying to perform an operation in Excel using Powerquery.

我想删除列表中的联系人，如果他们连接到某个 CompanyID 但列出的 CompanyName 不同.

I want to remove contacts in a list if they are connected to a certain CompanyID but are listed with a different CompanyName.

第一步是通过与上面的行进行比较来过滤掉它们.它们总是连续列出.

The fist step is to filter them out by comparing to the row above. They are always listed consecutively.

下一步是删除删除列中包含值 delete 的所有行，但不幸的是我没有到那一步.

Next step is to remove all rows containing the value delete in the delete column but unfortunately I do not get to that step.

我看过这篇文章 并尝试使用以下代码:

I have looked at this post and have tried with the following code:

= Table.AddColumn(#"Added index", "Delete", each if [CustomerID]= #"Added index"{[ContactID]-1}[CustomerID] and [CompanyName]<> #"Added index"{[ContactID]-1}[CompanyName] then "delete" else null)

但我收到此错误:

There were too many elements in the enumeration to complete the operation

我用谷歌搜索了错误，它似乎与重复的列名有关，但我没有.

I have googled the error and it seems to be connected with duplicated column names but I do not have any of those.

请帮忙！

推荐答案

如果#"Added Index"后面有操作数据的步骤，需要注意当前步骤将引用出现在 #"Added Index" 步骤中的数据.您在 #"Added index" 步骤之后执行的任何操作将无法用于您发布的语句.

If you have any steps after the #"Added Index" that manipulate the data, you need to be aware that the current step will refer to the data as it appears in the #"Added Index" step. Any manipulations that you performed after the #"Added index" step will not be available to the statement you posted.

您可能需要调整您发布的步骤以使用数据的最新表示形式，而不是 #"Added index".

You may want to adjust your posted step to use the latest representation of your data instead of #"Added index".

如果 #"Added index" 是您的 M 代码中的最新步骤，则该语句运行良好，因此问题在于数据是在其间的步骤中操作的.调整语句以引用最新的数据步骤而不是 #"Added index".

If #"Added index" is the most recent step in your M code, the statement runs fine, so the problem is that the data is manipulated in the steps in between. Adjust the statement to refer to the most recent data step instead of #"Added index".

这篇关于为什么对上一行中的值的引用会引发此 Expression.Error?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！