In this writing, I want to share how I learned to create a function in Power Query Editor in Power BI Desktop in order to get Running Total as a Group.
Running total is fairly easy to create in Excel. And when dynamic result is required, especially grouped by year or grouped by a certain category, it is straightforward to write DAX formula in Power BI.
However, when I tried to create a running total in Power Query Editor in Power BI, I spent quite a lot of time understanding it and creating it, especially with grouping concept.
Before I step further to show how I learned, I must admit that I still cannot find any relevant business cases that a running total column is needed to be created in Power Query Editor before loading to the data model. In many business cases, that I know so far that need running total, writing DAX to answer it is better (at least for me) from the perspective of dynamically-meeting-requirements and understanding-formula. So, if it is OK with you, please share in the comment and let me know what business cases select Power Query Editor to show and create a running total column.
Firstly, let's start with a simple one that needs Running Total without grouping.
The simple query looks like below.
Without thinking about the Category, steps to create a simple running total are like below.
Step 1: Decide an order to create Running Total: in this case, I decided to have Date-Ascending order.
Step 2: Select Date column, and sort it in Ascending order. Later, if you want to have reverse running total, you can think of selecting Sort Descending in this step.
Step 3: Add index column that starts from 1, and increases by 1.
Step 4: Add a customs column by using List.Sum function and List.Range function. How to understand and write List.Sum and List.Range functions is out of scope of this writing, but you can see how I wrote in the below screenshot.
Step 5: Inside Advanced Editor, it looks like below.
Note: Keep in mind that the important steps, that are going to be used later, are #"Added Index" step and #"Added Custom" step.
Step6: The result is as expected, like the below. We can see two more columns are added, that are Index column and Running total column (Result column).
The above is what I need to think before I move on further to create a function.
In order to create a function, I could think like below.
Input: Table format -> add index column
Output: by using the above table (Input), write a formula that uses List.Sum and List.Range to create a running total column.
The below is how I created Function Query by thinking like the above.
Step 1: Open Blank Query
Step 2: Open Advanced Editor, and write like below. The below steps after "let" is copied from the above (#"Added Index" and #"Added Customs"), and then I modified a little bit to show what I need.
Now, The Function Query (fxRunningTotal) is created, and I am ready to Group-by Category Column, and use the function (fxRunningTotal).
Start from the beginning.
Step 1: Group by Category column, and name the new column, "RT".
Step 2: Open Advaned Editor, and then add the steps like below in order to apply the created function (fxRunningTotal).
Step3: The result of each Table in RT column looks like below.
Step4: And then, I could simply expand the RT column to show the result.
Be noted that order to create the Index Column is important.
In this case I ordered it by Date Column Ascending. I could change it to Date Column Descending if business requires. If data does not contain Date Column, but the requirement is to show Running total by higher quantity to lower quantity, for instance, the quantity column can be sorted in descending order, and then the Index Column can be created.
For me, it took a day to create a function for Group-Running-Total. However, it was so exciting and fun to learn more about creating Function Query.
I hope this also helps readers to have fun in creating Function Query and using it for Group-Running-Total in Power Query Editor.
Comments