How to compare neihgbouring rows in the same column in Power Query

Question

Hello,

I have an alpha-numeric code, I want to compare it with the row below in the same column and concatenate results in the new column. Before doing that I sorted in ascending order. Some codes are unique, some codes have 2,3 ,4 and more 'copies'
In excel the formula is simple: B2&IF(B2=B3, "y",""). The dataset ranges between 1000-4000 rows.

In Power Query I tried.

= Table.AddColumn(#"Added Index", "New Column", each [Column] & if [Column] = Record.Field(#"Previous Steps"{[Index]+1}, "Column") then "y" else "")

I got the error Expression Error We cannot convert the value to Type List

Details:

Value=TYH03435

Type=[Type]

Could you suggest something?

I believe there are many ways to solve the case.

Answer 1

Hi

Your expected result is really unclear to me. I put the following Table in Excel and entered your formula in column D below:

If this is really what you expect with your Power Query step you can do it as follow (not ideal) assuming your previous step is #"Added Index":

= Table.AddColumn(#"Added Index", "New Column", each
    if [Index] = List.Max(#"Added Index"[Index]) then [Column]
    else if [Column] = #"Added Index"{[Index]+1}[Column] then [Column] & "y"
    else [Column]
)

Also look at Fast and easy way to reference previous or next rows in Power Query or Power BI for alternative