Removing all the last rows below the row that had met the condition in Power Query

[Ramh84]

Hello folks,

I am trying to find the position of the cell in Column1 that starts with the word "Technician".

Once found out, I would like to delete all the below rows with respect to (above found position) and including the row found with the word "Technician". Note that the # of rows below might vary..

Please note that sometimes the pdf pages are corrupted, and thus, might not have pulled up the word "Technician".

In such instance, I would like to do nothing but return the nested table as is. 

Please not that I am in the process of creating a custom function so that I use it as invoking custom function later on, as I am reading the pdfs from a folder that will contains many files..

My intitial thoughts as I am trying to implement above processes is to use the following Mcode functions:

= Table.AddColumn(#"Filtered Rows", "Custom.1", each Table.RemoveLastN([Data], List.PositionOf([Data][Column1],"Technician",Occurrence.First, (x,y) => Text.StartsWith(x,y))))

However, this definately will not acheive so, as it will truncate more records/rows than what should be..

I hope that I made it clear 🙂

Appreciate your support.

[Ramh84]

Thank you so much for your quick and accurate solution! @bhanu_gautam 

You made my day 🙂

[bhanu_gautam · Accepted solution]

@Ramh84 Try using below steps

Open Power Query Editor.
Go to the "Home" tab and click on "Advanced Editor".
Define a new function as follows:

let
// Define the custom function
RemoveRowsAfterTechnician = (inputTable as table) as table =>
let
// Find the position of the row that starts with "Technician"
position = List.PositionOfAny(
List.Transform(inputTable[Column1], each Text.StartsWith(_, "Technician")),
{true}
),
// Check if the position is found
resultTable = if position = -1 then
inputTable
else
Table.FirstN(inputTable, position)
in
resultTable
in
RemoveRowsAfterTechnician

After defining the function, you can invoke it on your table.
Assuming your table is named #"Filtered Rows", you can add a new step to invoke the function:

m
let
// Your existing steps
Source = ...,
#"Filtered Rows" = ...,
// Invoke the custom function
ResultTable = RemoveRowsAfterTechnician(#"Filtered Rows")
in
ResultTable