Remove relative rows dynamically

[sevenhills]

Hi,

Remove relative rows dynamically

In Power Query, I want to remove relative rows based on certain conditions. Once identifying the first row of a set, how to remove the row and the next relative rows dynamically?

Let us say, I am processing files in a folder and are combined in one table and as 

File 1 - 47th row as "first set" first row, 154th row as "second set" first row

File 2 - 42th row as "first set" first row, 154th row as "second set" first row

File 3 - 41th row as "first set" first row, 161th row as "second set" first row

I am able to identify the first row of each set of rows easily. Following 7 or 5 rows is no easy way to identify as it has dynamic text, except the fact fixed number of rows.

I need to delete the rows as 

       File 1 - Delete rows with rownumber as 47 +7 rows, 154+5 rows 

       File 2 - Delete rows with rownumber as 42 +7 rows, 154+5 rows 

       File 3 - Delete rows with rownumber as 41 +7 rows, 161+5 rows 

After getting the first row number in each set, How to remove relative rows dynamically?

TIA

[sevenhills]

Thank you it worked. 

[sevenhills]

I am able to get through Power Query "M" (at run time) as 

Delete Row Details as

File Name	Set Name	Row Begin	Row End
File 1	Set 1	47	54
File 1	Set 2	154	159
File 2	Set 1	42	49
File 2	Set 2	154	159
File 3	Set 1	41	48
File 3	Set 2	161	166

(FYI, I have over 100 files+ that will get into dataset and I can determine the rows that need to be deleted in each file in power query.)

Using the above table in power query, how to delete rows based on row numbers in the below dataset? 

File Name	Row Number
File 1	1
File 1	2
File 1	3
File 1	…
File 2	1
File 2	2
File 2	3
File 2	…
File 3	1
File 3	2
File 3	3
File 3	…

[AlexisOlson · Accepted solution]

Try adding a custom column to the table:

{[Row Begin]..[Row End]}

and then expand that column into rows so that it looks like this:

Then you can add an index column on your data table that indexes each distinct File Name and do a Left Anti join to remove any rows in that table that match the custom column in the expanded table above.