Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
carlacervonif
Frequent Visitor

Running Total or Cumulative Total column in Power Query

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.

2 REPLIES 2
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




zenisekd
Super User
Super User

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 🙂

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors