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

William 41 Reputation points

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.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,092 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points

    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:


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


    #"Grouped Rows" = Table.Group(<PreviouStepName>, {"Source.Name"}, {{"FOO", each Table.FirstN(_, 3), type table ...}})  
    • Expand the columns you want from column [FOO]:


1 additional answer

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points


    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 ...}}  
    0 comments No comments