Forum Discussion
How do I remove top rows above a word?
- 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.