Sum Every Nth Row in a Column

Let’s say you have a single-column list of transactions. You want to add up the amount values, which are on every say 5th row. This is a perfect task for Power Query. So, in this post, I’ll demonstrate how to set up a query that removes and keeps a defined number of rows in a repeating pattern.

Objective

Before we get too far, let me share a couple of images so we can visualize our task. First, here is an example of a list of transactions that are contained in a single column.

You’ll notice it is a repeating sequence, where the transaction ID is the first item (eg, 107240), the first name is the 2nd item (eg, Jessica), the last name is next, then the street, city, state, zip … and then the amount (eg, 223.5). Then there is a blank cell, and then the pattern repeats. 

Our goal is to add up the amount values from each transaction, which is the 8th item in each transaction. So, before Power Query, how would we approach this? Hmmm. We could try to do a series of filters. Or, perhaps copy/paste special > transpose on each transaction. Or, perhaps write a formula to conditionally sum or identify the amount values. Then, once we’ve finished these manual steps, we are good. Until next time … then we get to do these steps all over again. But, the good news is that Power Query makes this type of task very easy, this time and the next time we have an update. 

Power Query will help us transform the transaction list and keep only the amount values, like this:

Once we are here, it is easy to compute the sum. Or, if desired, we could even have Power Query compute the sum and just return it, like this:

So, now that we can see our goal, let’s do this thing.

Details

We’ll perform this transformation using these steps:

• Import Data into Power Query
• Remove Alternate Rows
• Compute Total

Let’s get to it.

Import Data into Power Query

Use the Data > Get Data command to locate your list of transactions. In our case, it is in an Excel worksheet so we use the From Table/Range command. 

We can see the data made it into the Power Query editor, as shown below.

Now it is time to define our pattern.

Remove Alternate Rows

We can see that the amount values are the 8th item in each transaction. We then see a blank (null) value after the amount. And then the pattern continues. That is, there are 9 total items in each transaction because we need to account for the null. So, at the end of the day, we want to remove items 1-7, keep item 8, and remove item 9. Our rule needs to account for all of the items in the repeating pattern. Good? Alright, so, we just click Home > Remove Rows > Remove Alternate Rows. The resulting dialog is shown below.

We tell Power Query to start at row 1, to remove 7 items, and then keep 2 items. We click OK and bam:

Then, we can apply additional patterns if needed. In this particular case, since the items we want to remove are all the same (null), it would be fast to just apply a filter. But, to demonstrate the idea of applying an additional pattern, we just click the Remove Alternate Rows command again.

This time, the first row we want removed is on row 2, and then every other row (so, remove 1 and keep 1). Bam:

And, now we have a clean list of the just the amounts. At this point, we could send the list of amounts back to Excel so we can add them up by clicking Close & Load To … Table. But, we can certainly have Power Query compute the sum as well. 

Note: you can keep applying additional patterns if needed by using the Remove Alternate Rows command multiple times.

Compute the Total

To compute the sum, we just click Transform > Statistics > Sum. Bam:

Then we just Close & Load To … Table, and bam:

But, perhaps the best thing of all is that tomorrow, when we get a new list of transactions, we don’t need to recreate all of those steps. All we need to do is right-click the results table and Refresh 🙂

If you have any other related Power Query tips, please share by posting a comment below.

Sample file: RemoveAlternate.xlsx