Forum Discussion
Running Sum by group for several columns with values
Community SupportHi Poloscopie
If your PROJ_DATE column doesn't have duplicated values for each combo snapshot/reference/factory, you can add a custom column with below code. Click fx icon beside formula bar to enter this code. Change previousStepName accordingly.
= Table.AddColumn(previousStepName, "Cumulative DEP", each List.Sum(Table.SelectRows(previousStepName, (x)=> x[SNAPSHOT]=[SNAPSHOT] and x[REF]=[REF] and x[FACTORY]=[FACTORY] and x[PROJ_DATE]<=[PROJ_DATE])[DEP]))
Modify above code for NEED and RESS.
= Table.AddColumn(previousStepName, "Cumulative NEED", each List.Sum(Table.SelectRows(previousStepName, (x)=> x[SNAPSHOT]=[SNAPSHOT] and x[REF]=[REF] and x[FACTORY]=[FACTORY] and x[PROJ_DATE]<=[PROJ_DATE])[NEED]))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hello v-jingzhang!
Thanks a lot for your answer.
In theory, the solution works, but as I'm dealing with a huge number of lines (> 1 to 2 millions), even adding one of the three columns with your solution seems to make the calculus too long (I never made it to the end).
The solution that worked for me in my original post but that I don't know how to adapt for three columns seems to work pretty well for big amount of lines.
You're using the List.Sum function while the other solution used the List.Accumulate function. I don't if the gain of perf is due to this.
- v-jingzhang
Hi Poloscopie
Yes, you are correct. My method doesn't have a good performance. If you want to use these data in a report, why not try using DAX to create measures or calculated columns to get the cumulative values? That may have a better performance.
At present I don't understand the solution in your original post, so will need some more time to find out how to adapt it for three columns. I guess its good performance is due to Table.Buffer. But I admit that I didn't fully understand it. This may be helpful: How does Table.Buffer work? | Power Query | Excel Forum • My Online Training Hub
Jing
