Forum Discussion

craig811's avatar
craig811
Helper III
6 years ago
Solved

How do I remove top rows above a word?

Hi,   Which M langugae or function can I use to remove top rows above text:GL JE Name in column1 ?   Thank you in advance
  • edhans's avatar
    6 years ago

    You have to find the word. List.PositionOf() will work.

     

    This is the function you'd use. It would return a number that you could then use in removing top rows. For example:

     

     

    let
        Source = Excel.CurrentWorkbook(){[Name="SampleTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test Column", type any}}),
        #"Find Position" = List.PositionOf(#"Changed Type"[Test Column],"GL JE"),
        #"Removed Top Rows" = Table.Skip(#"Changed Type",#"Find Position")
    in
        #"Removed Top Rows"

     

     

    In the Find Position step, I find the "GL JE" text in the "Test Column" column.

    Then in Removed Top Rows, I used the value found from #"Find Position" instead of a constant. Note that the Removed Top Rows refers back to the #"Changed Type" table then removes the rows referenced directly above in "#Find Position." M code doesn't have to move sequentially and I often jump around a bit to make code clearer. 

    You could make a more complex line but it becomes harder to read. For example:

    = Table.Skip(#"Changed Type",List.PositionOf(#"Changed Type"[Test Column],"GL JE"))

    But I personally find that is a bit harder to document and follow the logic 6 months later when I am trying to debug or edit.

     

     

    You can see my test file here. I just used Excel to quickly do this.