Today we are going to discuss about various methods to calculate running totals in power query. Creating expandable range is lot easier in excel formulas compared to M-Code in power query. But if we understand the concept of handling lists, reducing and expanding the size of list in power query, then we can open the doors of exciting stuffs in M-Code.
This is going to be M-Code feast, Lot to learn, let's dive in.
Here, we have date wise sales in the range B3:C17. To convert this data set into table, click inside the range and Press Ctrl + T.
Ensure 'My Table has Headers' check box checked in 'Create Table' dialog box and click OK. Menu bar -> Table design -> Table Name section, change the name as "RT" as shown in the picture.
Press Alt + A + P + T, to get table into power query editor.
Now, we are in power query realm,
List of queries in left pane,
Steps of each query in right pane.
Center part is for table and on top of table, you can see formula bar.
Data type recognized by power query and added a step as 'Changed Type'. Click on the name of the step 'Changed Type' and press F2 to edit. Rename based on your preference but avoid space in step name. We changed the name to ChType.
Syntax for referring the previous step name with space is,
= #"Changed Type"
Syntax for referring the previous step name without space is,
=ChType
For better readability, best practice is to avoid space.
Remember that; Power query is case sensitive; Base is zero.
Method 1:
Click on the fx button on the left side of formula bar to create a new step.
As soon as we click on the button, one step got added in query settings pane namely Custom1. In the formula bar, you see that, the formula refers to previous output.
Here, the output of the step 'ChType' is Table , so the formula '=ChType' extracts the output of previous step as the input for new step.
keep only equal sign in formula bar, delete the step name, will start the function from scratch. We are going to add index column in our table. So, type below formula
Arguments of this function are,
In this function, first argument asks us data in table format to add index column, we can use ChType.
2nd argument asks the new column name in text data type, feed "Index" there.
3rd argument asks for the initial value as number, feed 1 as our starting number.
4th argument asks for the increment as number, our requirement is to increase by 1, so either we can feed 1 or we can ignore the argument and close the bracket as shown below.
We changed the name of the step to 'IndexCol'. From the table named 'IndexCol', we are going to extract 'Sales' column using Field Access operator [] (Square brackets).
To do that, press on the fx button to create new step and type below formula.
Output of this function is list; you see the difference in logo for query name(RT) in left side pane between the previous picture and this picture. List in power query can only have one column. We are going to add this entire list in each row of 'IndexCol' table as additional column as shown below. Keep equal sign in formula bar and delete the function.
Start typing the below function in formula bar.
Arguments of Table.AddColumn function are,
For the 1st argument, we used 'IndexCol' table,
for 2nd argument, we used 'RT' as the new column name as text.
If we are adding the column, then we are going to do some operation in that column. So, this argument asks us to input the function to be carried out in each row of the column.
We used each key word to inform that this function to be iterated in each row, then
IndexCol[Sales], it means put entire sales column as list in each row, you can see the list in each row of RT column. From now on we are going to work with this list to get our desired result.
We have to shrink the list in such a way that,
First one item is there in the list in the first row of RT column, then
First 2 items are there in the list in 2nd row of RT column, then
First 3 items are there in the list in 3rd row of RT column and so on.
If we pick the [Index] column value of the same row, we can use it as count of our list 1,2,3..
We have List.FirstN function in Power query to accomplish our task. Let me see how we can use that here. Arguments of this function are,
We are going to use above list, here in the list argument and for the count, we are going to use the same row value in [Index]Column as shown below,
List in each row is shrinked based on the Index column value. Now you understood why we added Index column earlier. If somehow, we managed to sum the values of list in each row, then we can get running total, for that List.Sum function going to help us.
We got running total in each row and we renamed our last step as 'RT'.
Work of [index] column is done, so we are going to delete the column, before loading the table in excel as shown below.
Then, change the data type of 'Date' column from datetime to date,
data type of RT column from general to decimal number as shown below.
In home tab, left most side, drop down is there in close and load option, click on that and select close and load to option as shown below.
Then, import data dialog box will open as shown below,
select table option at the top, and select existing worksheet or new worksheet option to load the table in excel. If you select Existing worksheet option, then select the cell where we want to load the table and click OK.
In the right side 'Queries and Connections' pane you can see the name of the table and how many rows are loaded in the table.
Method 2:
In method 1 we used List.FirstN function, here we are going to use List.Range function.
Double click on RT query in queries and connections pane in right side to launch power query editor.
In queries pane in left side of power query editor, right click at RT query and select Duplication option as shown above. Rename query name based on your preference.
In applied steps pane at right side, right click over the step RT and select Delete until end option to delete the steps after IndexCol.
In method 1, we were already here, but this time, from now on we are going to travel in different direction to manipulate the list. We are going to use List. Range function.
This is like OFFSET function in Excel.
Arguments of this function are,
For the first argument, we already have the list,
For the 2nd argument,
at each row in RT column, our list is going to start at 1st item. We don't have to offset anything, so we will feed 0 in 2nd argument.
For the 3rd argument, we can use the corresponding row value in [Index] column as we did in method 1.
Now, the list items count is managed by corresponding row value in [Index] column. In the 4th row of RT column 4 items are there in the list. In excel, what we call is expandable range.
Now we can get the sum using List. Sum function as shown below.
Then, we rounded the numbers into 2 digits using Number. Round function.
Delete the Index column, change the data type of RT column from general to decimal number, Date column from datetime to date and load it as table in excel as shown below.
Comments