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

[Anonymous]

How to dynamically delete Top Rows before a specific value(ex: ID Number) in Column1 in the below example :

I have data set like below. Sometimes the Rownumber of "ID Number" changes. Havning difficulty to delete TopRows using GUI function in Power Query. Can someone help how to find the "ID Number" column using Index or find function. 

Below is sample data. Appreciate any help on this!

unwanted
more
unwanted
ID Number	Amount
12	3
23	4
12	5
23	6
12	7
23	8
12	9
23	10
12	11
23	12

[Anonymous · Accepted solution]

Start by removing rows from the gui by putting in any number. Power query will write a line like below.

#"Removed Top Rows" = Table.Skip(Source,2)

Modify it to be the function each [Column1] <> "ID Number"

#"Removed Top Rows" = Table.Skip(Source,each [Column1] <> "ID Number")

Note that this code will work as long as all of the data in the column is text.

[Mariusz]

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"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

I was still trying to understand how was the source getting feed. How was json is converting to table.

But it worked as well.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this code is created automatically by Power BI when you manully input data. I prefer to do this way to give everything the possibility to understand the solution. In future I will use the #table function to make it more clearly.

If my post works as well, I would appreciate it you marking it as solution as well or to give a like.

All the best

Jimmy

[Anonymous · Accepted solution]

Start by removing rows from the gui by putting in any number. Power query will write a line like below.

#"Removed Top Rows" = Table.Skip(Source,2)

Modify it to be the function each [Column1] <> "ID Number"

#"Removed Top Rows" = Table.Skip(Source,each [Column1] <> "ID Number")

Note that this code will work as long as all of the data in the column is text.

[matbau]

Thanks for this. What a great Resource. I am struggling to filter some data that comes with two clients.

[Anonymous]

Thank you, simple and straight forword solution. 

[Anonymous]

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

[Jimmy801]

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