Why does a reference to a value in previous row th

问题:

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

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.

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)

But I get this error:

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.

Please help!

回答1:

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.

You may want to adjust your posted step to use the latest representation of your data instead of #"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".