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

William 41 Reputation points
2021-03-07T21:21:39.567+00:00

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

75139-image.png

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
    2021-03-08T06:17:18.243+00:00

    Hi @William

    When your there (in PQ Editor):

    75217-beforegrouping.png

    • 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]:

    75128-expandicon.png


1 additional answer

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-03-09T20:36:39.133+00:00

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