Power Query remove all rows below a field (Cell) value

[DMfba]

Hi All,

I have an excel file that I am bringing into power query that I need to clean up. What I am trying to do is remove all rows below a certain field (cell) value. The issue is the number of rows will change over time and the field value will be in a different row. I have a few different files that I would like to apply this process too. The target cell value for this file is Total Currently recorded.

Basically when the M code sees the value "Total Currently Recorded" remove all rows below that row.

Here is the portion of a code that I was trying to use, but returns and error The column “Vendor” of the table was not found. The blue text is where I am trying to find the cell value.

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Buyer", type text}, {"Vend #", Int64.Type}, {"Vendor", type text}, {"Details", type text}, {"Column5", Currency.Type}, {"Column6", Currency.Type}, {"Column7", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column5", "Fund"}, {"Column6", "GL"}, {"Column7", "Date"}}),

[COLOR=rgb(44, 130, 201)]YTD_Pos = List.PositionOf(Table.Column(Source,"Vendor"),"Total Currently Recorded"),

RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)[/COLOR]

in

RemoveRows

Does anyone have any suggestions on how to solve this.

Thank You
DM

 

[JGordon11]

Try changing Source to #"Renamed Columns"

also I think your offset and count should be

YTD_Pos+1,Table.RowCount(#"Renamed Columns")-YTD_Pos-1

so

YTD_Pos = List.PositionOf(Table.Column(#"Renamed Columns","Vendor"),"Total Currently Recorded"),
RemoveRows = Table.RemoveRows(#"Renamed Columns",YTD_Pos+1,Table.RowCount(#"Renamed Columns")-YTD_Pos-1)

 

[DMfba]

Hi JGordon,

Thank you for the help. That seems to work. I'm not sure what I missed I think I had the "" before the # as I started with #"Renamed Columns", but the offset you updated works perfectly. I am going to test the other files as well.

Thank you again.
DM