Remove row with incorrect value

[josephrandall]

See picture below. There is one row (highlighted) that the value is wrong. Is there anyway I can remove this row via formula?

Basically if the values of the row above and below are equal to each other but different to a cell inbetween, remove that row.

Thanks for any input.

[KeyurPatel14]

If you dont want to make a custom function then you can try the below M Code also it will also work if the data increases in future.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwNDE0NTM1MTJWitUZSL4JnfmmFPLN6Mw3p7J+UvkWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Pvalue", each try #"Added Index" [Data] {[Index] - 1} otherwise 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Nvalue", each try #"Added Index" [Data] {[Index] + 1} otherwise 0),
#"Added Custom2" = Table.RemoveColumns(Table.SelectRows(Table.AddColumn(#"Added Custom1", "Result", each if [Pvalue] = [Nvalue] and [Pvalue] <> [Data] then 0 else [Data]),each ([Result] <> 0)),{"Index", "Data", "Pvalue", "Nvalue"})
in
#"Added Custom2"

[artemus · Accepted solution]

Here is a function you can use to add the next and previous row values as columns to your table.

You would invoke it like:

Table_Window(MyQuery, {-1, 1}, {"id})

This will add "id-1" and "id+1" columns

// Table_Window
let fn = (table as table, offsets as anynonnull, columns as anynonnull)  =>
    let columns = if not (columns is list) then {columns} else columns in
    let indexColumnName = Text.NewGuid() in
    let table = Table.AddIndexColumn(table, indexColumnName, 0) in
    let withIndexFn = (table as table, offset as anynonnull)  =>
        if offset = {} then
            table
        else if offset is list then
            @withIndexFn(@withIndexFn(table, offset{0}), List.Skip(offset))
        else
            let indexColumnName2 = Text.NewGuid() in
            let table2 = Table.SelectColumns(table, columns) in
            let table2 = Table.AddIndexColumn(table2, indexColumnName2, -offset) in
            let newColumnNames = List.Transform(columns, each _ & (if offset > 0 then "+" else "") & Number.ToText(offset)) in
            let table2 = Table.RenameColumns(table2, List.Zip({columns, newColumnNames})) in
            let joinedTable = Table.Join(table, {indexColumnName}, table2, {indexColumnName2}, JoinKind.LeftOuter) in
            let joinedTable = Table.RemoveColumns(joinedTable, {indexColumnName2}) in
            let joinedTable = Table.Sort(joinedTable, indexColumnName) in
            joinedTable
    in
    let table = withIndexFn(table, offsets) in
    let table = Table.RemoveColumns(table, {indexColumnName}) in
    table
in
   fn

[josephrandall]

thank you, kindly.