Forum Discussion
Solved
Delete Row with condition
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 le...
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,
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,