Power Query: Totals by Type
14 July 2021
Welcome to our Power Query blog. This week I look at selective running totals.
I have some tent data (yet again).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I am going to create some running totals. I start by loading my data to Power Query using ‘From Table/Range’ from the ‘Get & Transform Data’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I take the defaults and load my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I will start by creating a running total using List() functionality, as I did in Power Query: Keep On Running. To achieve this, I start by adding an Index column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I’ll start from one [1].
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
Now, I can create a Custom Column to calculate the running total.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This gives me the running total.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I can then add list buffering to stop it from reading the list of amounts every time it calculates a running total field.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
However, now I want to see the running total for each Tent Type. The method I will use this week is specific to this dataset. I will look at a more generic method next week.
I start from the Added Index step. I need to sort my data by Tent Type and then by Index.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
Having done both sorts, my data is ready for the next step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I am going to use similar M code to the overall running total, which was:
= List.Sum(List.Range(#"Buffered List",0, [Index])))
However, this time I want the values in [Index] to only increment within each Tent Type, and instead of using an offset of zero, I will offset to read the values for each Tent Type.
I start by creating a new Index column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Next, I set up the column I will be using as the offset in my calculation. From the ‘Add Column’ tab, I insert a column which is Index.1 divided by seven [7].
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image12.png/a1537847463e660a31158c8032525438.jpg)
I just need to tidy up these columns now. Using the Transform tab, I apply modulo seven [7] to Index.1, and add one [1] to it. I also round down Division.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image13.png/917da985be13220165c8d2823e95344f.jpg)
Now I am ready to apply the list functions to get my running totals.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M code I have used is:
= List.Sum(List.Range(#"Added to Column"[Amount],[Division]*7,[Index.1]))
I am summing up the amounts for my data in groups of seven [7].
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
The amounts are correct. I can sort by the original Index to restore the order and remove the columns I used to work towards the result.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/241/image16.png/d082e3477129350b8a2a589156028e63.jpg)
Next time I will look at another method, which doesn’t rely on the data being in groups of seven [7].
Come back next time for more ways to use Power Query!