Forum Discussion
Problems creating variable in advanced editor
Long story short I have some data that could start on any row in an excel sheet, but the data block always has a header that reads, "Client Ranking." And so to make sure I'm not reading a bunch of useless junk I'm creating a variable based on a filter and then removing the top X rows based on that variable. It looks something like this:
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Column1] = "Client Ranking")),
DataStartRow = #"Filtered Rows"{0}[Index],
#"Removed Top Rows" = Table.Skip(#"Added Index",DataStartRow),
I had to create the variable in the and remove top rows based on that function in the advanced editor because I don't know how to do it otherwise. However, if I add the variable like that when I close out of the advanced editor it removes all of my steps on the right. Removing the row with the variable definition brings my steps back, but I need that variable. This is based on a tutorial and I'm doing it almost exactly the same way, but it works in the tutorial and not for me. It seems like I'm doing something wrong but for the life of me I can't figure it out, any help would be greatly appreciated.
Hello Anonymous
There is a smart technique to solve this issue without adding new column. Just combine List.PositionOf and Tabl.RemoveFirstN. Here an example. By the way.. it would be also possible if you wouldn't know the exact column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyirNy1aK1YlWyi/JSC1SgHNBDGM4ywzMSspJBCIw0zknMzWvRCEoMS87My8dLBSZX1rkkliSGJRfboguYIQuADQ6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), DeleteUntilClientRanking = Table.RemoveFirstN(Source,List.PositionOf(Source[Column1],"Client Ranking")), PromoteHeader = Table.PromoteHeaders(DeleteUntilClientRanking, [PromoteAllScalars=true]) in PromoteHeaderCopy 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
5 Replies
Hi Anonymous
Do you have table like below?
You want to remove the top n rows which [column1]="Client Ranking", right?
for example, remove top 2 rows, then the final output should be 3~11 rows.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Anonymous
this is exactly what I proposed
Initial Data
Final data after query
so everything was removed above client ranking and client ranking made as header. If you don't want client ranking as header, just remove last step
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello Anonymous
There is a smart technique to solve this issue without adding new column. Just combine List.PositionOf and Tabl.RemoveFirstN. Here an example. By the way.. it would be also possible if you wouldn't know the exact column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyirNy1aK1YlWyi/JSC1SgHNBDGM4ywzMSspJBCIw0zknMzWvRCEoMS87My8dLBSZX1rkkliSGJRfboguYIQuADQ6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), DeleteUntilClientRanking = Table.RemoveFirstN(Source,List.PositionOf(Source[Column1],"Client Ranking")), PromoteHeader = Table.PromoteHeaders(DeleteUntilClientRanking, [PromoteAllScalars=true]) in PromoteHeaderCopy 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
