How to dynamically delete Top Rows *Before a specific value in a Column1 in the below example.

Hello Anonymous 

use Table.RemoveFirstN and List.PositionOf to solve this issue. Here an example what I mean, supposing your column name is "Column1". By the way it would be also possible to solve it when you even don't know the column name.

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs0rT8wrSU1R0lFSUIrViVbKzS9KhXMwZD1dFPxKc5NSi4Aijrn5pXklYGFDIyDfGMw0MgYyTRCipghRM4SoOULUAiFqiRA1NEAIGxoiiRspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    DeleteUntilIDNumber = Table.RemoveFirstN(Source,List.PositionOf(Source[Column1],"ID Number")),
    PromoteHeader = Table.PromoteHeaders(DeleteUntilIDNumber, [PromoteAllScalars=true])
in
    PromoteHeader

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

After initially loading the table, you could select the "ID Number"column, and right click and "Add as New Query". Rename it named SkipRows, or something, then:

//Add these steps to the new single column (you might have to make it a table first, I'm not at my PC!

AddIndex=Table.AddIndexColumn(Source, "Rows", 0,1),

FindRow=Table.SelectRows(AddIndex, each [NameOfIDColumn] = "ID Number")

Then, click right click the number in the "Rows" column and select "Drill Down", which will give you a number value.

Now, go back to your main table, and after your source step, add:

Skipped = Table.SkipFirstN(Source, RowsToSkip),

The RowsToSkip value should now be the zero based index number of the row with "ID Number", and since it starts at zero, the number of rows to skip will match up with the "Rows" number.

I think Ken Puls might have written something like this at some point.

-Nate

Hi Anonymous 

You can try the below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs0rT8wrSU1R0lFSitWJVsrNL0qFsdHlPF0U/Epzk1KLgAKOufmleSVgYUMjIN8YzDQyBjJNEKKmCFEzhKg5QtQCIWqJEDU0QAgbGiKJGynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID Number", Int64.Type}, {"Amount", Int64.Type}})
in
    #"Changed Type"