Forum Discussion
How to dynamically delete Top Rows *Before a specific value in a Column1 in the below example.
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.
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
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