Problems creating variable in advanced editor

[Anonymous]

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.

[Jimmy801 · Accepted solution]

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

[v-juanli-msft]

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.

[Anonymous]

Not exactly, I want to remove everything above the header "Client Ranking." That header essentially marks the start of my data, and there is some useless stuff above it.

[Anonymous]

Just issue the line below. Just substitute your name for PriorStep.

=Table.Skip(#"PriorStep",each [Column1] <> "Client Ranking")

[Jimmy801]

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