Running Total or Cumulative Total column in Power Query

[carlacervonif]

Please please PLEASE make it possible to create a running/cumulative total column in Power Query. The amount of hoops I have to go through to get something as simple as a cumulative value column is crazy. Power BI is able to do things that are ALOT more complex than a running/cumulative total column so I'm confused as to why it has to be so difficult. Even after searching for solutions for HOURS, I have not found a solution for a running/cumulative total column in Power Query without having to copy and/or group a table.

[BA_Pete]

Hi @carlacervonif ,

The reason this topic doesn't seem to get discussed/solved very often is because Power Query isn't really designed for this type of operation. It's a transformation/mashup tool. DAX within a data model is designed to make calculations such as this very fast and efficient.

That being said, it is possible. The trick with doing it in Power Query is doing it with a single table scan, otherwise it can perform very poorly over larger datasets. Here's a good reference to get you started, but it may depend on your specific data structure and needs:
https://gorilla.bi/power-query/running-total/#fast-running-totals 

Pete

[zenisekd]

Hi, 
a) if you want to make a suggestion, I suggest you post it to Ideas, where it gets votes and better highlight for Microsoft. https://ideas.fabric.microsoft.com/
b)

To create a cumulative total in Power Query, you can follow these steps:

1. Load your data into Power Query: Import your data into Power Query from your data source. This could be from an Excel table, CSV file, database, or other sources.

2. Add an Index Column: Go to the "Add Column" tab in the Power Query Editor, then select "Index Column" and choose "From 0". This will add a new column with a sequential index starting from 0.

3. Sort the data: Sort your data by the column you want to create the cumulative total for. You can do this by clicking on the column header and selecting "Sort Ascending" or "Sort Descending" as appropriate.

4. Add a Custom Column for the Cumulative Total:

   • Go to the "Add Column" tab.
   • Click on "Custom Column".
   • In the formula bar that appears, enter a formula to calculate the cumulative total.
   • For example, if your column containing the values you want to cumulate is named "Value", you can use the following formula: = List.Sum(List.FirstN([Value], [Index] + 1))
   • Click OK.

5. Expand the new column: Click the expand button (double-headed arrow) on the header of the newly created custom column. Uncheck the "Use original column name as prefix" option if you don't want the original column name to be added as a prefix.
   
   

There you go. 

Kudos and mark as solution appreciated 🙂