Add Column recalculates on Filter Column - faster option?

[Grosskopf]

I have a query that compares a value in a row to the same value in a previous row. If the value is the same in the previous row the Add Column value is set to 0, if the value is different to the value in the previous row the Add Colum value is set to 1.

This works well but it does take a bit of time when comparing thousands of rows.

My code for adding the column that is to be filtered:

#"Add SpeciesChange" = Table.AddColumn(#"Reorder Columns", "SpeciesChange", each if #"Added Index"{[Index]} [Index] = 0 then "1" else if #"Added Index"{[Index]-1} [Name] = [Name] then "0" else "1")

My problem is that the calculation for SpeciesChange values appears to be re-run when I filter the list to show only 1's. The Filter step also takes much longer than the original Add Column calculation.

My code for filtering the SpeciesChange list:

#"Filtered Rows" = Table.SelectRows(#"Add SpeciesChange", each ([SpeciesChange] = "1"))

Is there a better way to do this so that is significantly faster?

[wdx223_Daniel · Accepted solution]

NewStep1=Table.Buffer(#"Added Index"),

NewStep2=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Reorder Columns"),{},(x,y)=>if NewStep1{y[Index]}[Index]=0 or NewStep1{y[Index]-1}[Name]<>y[Name] then x&{y} else x))

[Grosskopf]

Thanks Daniel, that was perfect. Quick copy and paste and we're done.

Now I just have to work out what that code means 🙂