Excel Data-Power-Query-Editor delete every nth row from a specific source name

Question

I have a question about excel power query editor. I have a sheet that looks like this:

My question is: Is it possible to only keep the first 3 rows of each book, and delete the rest? I have a pretty long sheet, so doing it manually would take days of hard work.

Answer 1

Hi @William

When your there (in PQ Editor):

• Right-click on column [Source.Name] > Group By... > New Column Name = i.e. FOO > Operation = All Rows > OK
• Advanced Editor > Change the step #"Grouped Rows" as below > Done:

From:

#"Grouped Rows" = Table.Group(<PreviouStepName>, {"Source.Name"}, {{"FOO", each _, type table ...}})  

To:

#"Grouped Rows" = Table.Group(<PreviouStepName>, {"Source.Name"}, {{"FOO", each Table.FirstN(_, 3), type table ...}})  

• Expand the columns you want from column [FOO]:

Answer 2

@William

Q: What if i want to delete the first 3 rows, keep the next 3 rows, and delete the rest of the rows for each book?
A1: Skip the first 3 (Table.Skip) rows then take the first 3 (Table.FirstN) rows

#"Grouped Rows" = Table.Group(<PreviouStepName>, {"Source.Name"},  
   {{"FOO", each Table.FirstN(Table.Skip(_, 3), 3), type table ...}}  
)  

A2: Take the first 6 rows then skip the first 3 rows:

#"Grouped Rows" = Table.Group(<PreviouStepName>, {"Source.Name"},  
   {{"FOO", each Table.Skip(Table.FirstN(_, 6), 3), type table ...}}  
)