Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 us...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

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