Delete rows downward

Hi Boricuanet,

 

How about this:

Before:

 

 

After:

 

The idea is:

1) create new index column:

 

2) Create new column, find the value "Pedro" and stamp it with 1:

 

 

3) create a running total column:

 

4) Filter for 0 in running total column:

 

 

5) remove all unnecessary columns:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPVYrVAdKJydmVEFZqcgaYUQISKgHy9JJy8tPBQgGpKUX5qJKovBKQcbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column] = "Pedro" then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Running Total", each List.Sum (List.FirstN(#"Added Custom"[Custom],[Index]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Running Total] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom", "Running Total"})
in
    #"Removed Columns"

 

 

 

 

 

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/