Delete Row with condition

[fairooset]

Hi,

Here I have one table, my task is to delete yellow highlighted rows in below table. My condition is to delete each following rows after matches "Yes" in the Colum-3 till  column-1 value reach less than or equal to column-1 value of previous "Yes" row.

[Payeras_BI · Accepted solution]

Hi @fairooset,

Maybe it is not the most elegant way to solve it, but it seems to work.

From this:

to this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUfLLV4rViVYyBDIjU4vBbCRhJKYxqmIkBTB9xlgVm+DUh8aMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column -1" = _t, #"Column -3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column -1", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [#"Column -3"] = "Yes" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"All", each let Yes = try Table.SelectRows(_, each [#"Column -3"] = "Yes")[#"Column -1"]{0} otherwise Number.PositiveInfinity in Table.Combine({Table.SelectRows(_, each[#"Column -3"] = "Yes"), Table.RemoveFirstN(Table.SelectRows(_, each[#"Column -3"] <> "Yes"), each [#"Column -1"] > Yes)}), type table [#"Column -1"=nullable number, #"Column -3"=nullable text, Index=number, Custom=nullable number]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Other Columns", "All", {"Column -1", "Column -3"}, {"Column -1", "Column -3"})
in
    #"Expanded All"

Regards,