Power Query: All Over Running Totals
10 February 2021
Welcome to our Power Query blog. This week, I return to the running totals example from last week, to apply a more complex solution.
Last week, I looked at using List.Buffer() to speed up the running total calculation, so that the list created by List.Range() was buffered and not unnecessarily recalculated each time.
Whilst the list of values were buffered, the index count was not, and this too added to the time taken to perform the calculation. Maintaining the position on the index column to calculate the correct list is a difficult concept to buffer. Instead, I will look at another method to create a running total.
The M function I will use to drive this is List.Generate():
List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list
This generates a list of values given four functions that generate the initial value initial, test against a condition condition, and if successful, select the result and generate the next value next. An optional parameter, selector, may also be specified. This function creates list entries based on a starting value and conditions. The process loops until the list is complete.
I will create a new query, which I will be identifying as a function.
I start from the ‘Get & Transform’ section of the Data tab, where I opt to create a New Query. From the dropdown, I select ‘From Other Sources’, and then ‘Blank Query’. I access the Advanced Editor and enter the M code I need, to create a running total function that will work on any list of number values.
The M code I have used is:
(values as list) as list =>
let
RT = List.Generate ( () => [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each [RT = [RT] + values{[counter] + 1}, counter = [counter] + 1],
each [RT] )
in
RT
Essentially, this takes a list of values, and instead of an index column, it uses a counter to work through the list creating a running total (RT) and then increments the counter to move to the next value in the list.
If I click ‘Done’ in the Advanced Editor, my new query is created; I will call it FxRunningTotal.
I can now use this in my original query.
I make a duplicate of my query so that I can compare the results.
I remove all the steps from ‘Added Index’ downwards.
I will now add my function using the Advance Editor.
The M code is currently:
let
Source = Table.NestedJoin(ACCT_Order_Charges,
{"Item_Key"},
Items,
{"Item_Key"},
"NewColumn",
JoinKind.LeftOuter),
//single comment line
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Item_Group"}, {"NewColumn.Item_Group"}),
/* comment section
with many lines */
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.Item_Group", "Item_Group"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Order_Key", Order.Ascending}, {"Order_Line_Number", Order.Ascending}}),
#"Inserted Multiplication" = Table.AddColumn(#"Sorted Rows", "Order_Line_Number x Amount", each List.Product({[Order_Line_Number], [Amount]}), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Multiplication",{"Order_Line_Number x Amount"})
in
#"Removed Columns"
After step #”Removed Columns” I add the following lines:
BufferedValues = List.Buffer(#"Removed Columns"[Amount]),
RT = Table.FromList(FxRunningTotal(BufferedValues),Splitter.SplitByNothing(),{RT}),
Columns = List.Combine({Table.ToColumns( #"Removed Columns"), Table.ToColumns(RT)}),
ConvertToTable = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Removed Columns"), {"Running Total"}}))
The new lines create a buffer, then create a table with the running total in it. As I have other columns, I then need to get my columns and combine them with the new Running Total column, and then convert it all back to a table. Finally, I change the ‘in’ statement to reflect the last step. I click ‘Done’ to commit the new steps:
My running total appears. The next test is to load it into Excel:
The load was significantly quicker!
Come back next time for more ways to use Power Query!
