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
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