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
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"
Mariusz
If this post helps, then please consider Accepting it as the solution.
I was still trying to understand how was the source getting feed. How was json is converting to table.
But it worked as well.
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