Power Query: Tidier Totals by Type
21 July 2021
Welcome to our Power Query blog. This week, I take another look at selective running totals.
Last time, I extracted running totals by Tent Type for my tent data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
Although I got to my required output, I had to make an assumption that I had seven [7] rows for each Tent Type (since I had seven months of data).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I will start from the Added Index step that I used last time.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Since I am going to be reading the data in the table, I buffer it in step Custom1.
= Table.Buffer(#"Added Index")
The Custom Column I am going to create this time is a little more complicated, and is based upon Table() functionality instead of List() functionality. This allows me to enter conditions.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The M code I have used is:
= (Other_Table) =>Table.SelectRows(Custom1,
(Total_Table) => Total_Table[Index] <= Other_Table[Index]
and Total_Table[Tent Type] = Other_Table[Tent Type])
This looks complicated, but it can be broken down. Custom1 is the table from the previous step. Total_Table is the data that I am grouping. It’s essentially looking for all the rows where the Tent Type matches, and the Index is less than or equal to where we are now. Other_Table is the current table. The result will be a mini table, with all the rows that should be included in the running total.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I have grouped the data by type into these mini tables. Now I can expand them to get at the amounts.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This gives me a subgroup for each occurrence of Tent Type containing the amounts I need to total for the running total.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I can group these by Index.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Since the values in Month, Tent Type and Amount are the same for all occurrences in the group, I use Max (I could also use Min).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/242/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
The results are the same as last week, but this time I didn’t need to assume anything about my data, so this query would cope with additional months being added.
Come back next time for more ways to use Power Query!