Please note javascript is required for full website functionality.
MVP

Blog

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:

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).

I will start from the Added Index step that I used last time.

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.

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.

I have grouped the data by type into these mini tables.  Now I can expand them to get at the amounts.

This gives me a subgroup for each occurrence of Tent Type containing the amounts I need to total for the running total.

I can group these by Index.

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).

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!

Newsletter