Delete rows downward

[Boricuanet]

Hi all,

Does anyone know how I can delete all rows down according to data found? Ex: In the column names I search for "Pedro" and if it finds it delete all the rows downwards, is it possible in power query?

I am grateful for any suggestions or assistance

[Boricuanet]

Thank you very much, I was unaware of this formula.
At the moment I have an error in "Added" but it must be because of the language.
Thank you very much for your help

[watkinnc]

You could just add an index column, name that step Table1, then add quick GUI filter for "Pedro", then drill down on the index number, rename that step RowsToKeep. Then add a new step, and use

= Table.FirstN(Table1, RowsToKeep)

--Nate

[tackytechtom · Accepted solution]

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/