Power Query Running Totals - The Right Way!
Vložit
- čas přidán 22. 11. 2020
- There are a few ways to create a running total in Power Query, but most of them are slow. In this video I'm going to show you the slow way, and the right way, that's super fast to calculate. Download the Excel file here: www.myonlinetraininghub.com/q...
The final query shown in the video is called "Multi-Col Table Buffered ListGenerate".
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy - Věda a technologie
Hi Mynda. Thanks for the advanced lesson on running totals in Power Query. Always more to learn and practice :)) Thumbs up!!
Cheers, Wayne!
List.Generate is genius! Thank-you so much Mynda and Philip.
Glad you like it!
Excellent video Mynda. Thanks a lot for sharing your job.
My pleasure, Ivan!
Really great. It's complex but you made it easy to follow. Thanks so much!
Glad you liked it!
You're a lifesaver Mynda. Thank you so much!
You're most welcome, Pratik!
Thanks a lot for making such wonderful videos. I did not know about List.Generate!!! Could not resist thanking you
So nice of you 🙏
Thanks Mynda & Phil for the code - great content as usual
Our pleasure!
Thank you very much for your clear explanation. It helps me optimization of the current calculation. You are the best!
Great to hear!
I'm a seasoned Power Query User, but this is a lifesaver for current edge case! Thank you
Great to hear!
Finally got round to wrapping my head around List.Generate.
Powerful stuff and very versatile. I agree: this is a linear effort formula for calculating a CumSum. This has to be the most efficient way...
And that List.Buffer trick makes a huge difference, too.
This is a great lesson!
Glad it was helpful, Geert!
Marvelous! Thanks, Phil, for the technique (enjoyed the blogpost). Thank, Mynda, for the demonstration.
Cheers, Jim!
@@MyOnlineTrainingHub Cheers!
Wow!... Optimization is really the key to achieve this... and you explain exactly how to do it!.. Thank you so much!!
Glad it was helpful! 🙏😊
Amazing!!! I comprehended only parts of it, so I'll download the file for future use and learn too. Thanks for sharing
Great to hear!
Hi Mynda!Great Explanation Of The M CODE... Thank You And Thank You Phil :)
Cheers, Darryl!
Thank you for this video, just solved my problem!
And by adding several indexes, I can now run different slicers on running totals according to the users perspective on sorting😄
Fantastic!
you are the best Mynda !
Glad you liked my video :-)
Really advanced and really need to understand the syntax. Thanks for sharing
Glad it was helpful!
Thanks Mynda - that is really useful
Glad you think so!
Very impressive Mynda!
Thanks so much, Chris!
Thank You for the video, it is clear and straight to the point.
Great to hear 🙏
Thanks a lot for this great video!
In my case I have the topic to calculate the RT per shift. So my calculation has to start from new with the first number of the next shift. The shift itself ist listed in a separate column. Is there a way to do this?
It was a great explanation!
Thanks so much, Andres!
Great Mynda. Thank you 👍
My pleasure, Maki!
Excellent Very Good! Just what I needed... tks;👏
Great to hear!
Loved it, thank you so much
So glad!
Super!! Pretty advance but this is a great way to get into this problem when millions of records have to be calculated. I'm working on using this technique on the scenario of having different products which need to get a running total for each one! Thanks a lot!
So pleased it was helpful :-)
Wow, I got lost but sound amazing , have to watch again and again
Thanks, Jatinder! Please download the workbook and have a closer look at the examples.
Hello Mynda, thank you so much for your videos, I had learn so much from your video in the past couple of years! I do have a question for you on this running total, I used this method in one of my Power BI, and it had been working great, however, my analysis is getting more complex and I had to run the running total function over 5 times within the same query, and right now my query takes over an hour to load. I am wondering if I have to clear the buffer before I use the same rolling total function again? Thank you.
Hi, Thank you for the video, it is amazing!
I tried to used on query after merging and for some reason does not work, do we need some extra lines in the code?
Wow
Time Saver
Thanx
Glad you liked it :-)
You are awesome 😎
Glad you liked my video 😊
Thank you very much for this video.
Though I find it troubling that something as simple as a running total requires so much effort in PQ. In Python, it is simply cumsum() and you're done.
While you can calculate a running total in Power Query, it isn't really designed to aggregate data. It's a data gathering and cleaning tool. Whereas PivotTables are where running totals are easy.
Wow. That's all really impressive. I comprehended only parts of it, so I will download the file for future use because I would have no chance of ever re-creating it. My question is why doesn't MS just create a Running Total function and put it in the list of MCode functions and in the ribbon?
I agree, a running total function would be super helpful!
Hi, Great Video - very helpful. I have an expanded request based on this. When we have the running total I need a column that shows the max of the previous 5 days totals, and that again is a running total, so everyday show me the max of the previous 5 days rolling total. This is for PNL and so the rolling total goes up and down on a daily basis. In a normal spreadsheet that is easy, but in Power Query in Excel I can’t get the logic. It should be close ish to what you have done here? Any idea anyone on the formular/setup I need to achieve that?
Hi Mark, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
This is "running totals" Deluxe:-) Thank you!
You're welcome!
Dear Mynda, great explanation. Thank you. I have the tricky challenge to reverse a monthly running total (ytd values) to monthly values...if you have a nice solution in PQ you would help me so much:) Kind regards
Thanks, Dennis! We actually had a question like this recently on our forum and Phil wrote a custom function for it. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks :)
Welcome!
Hi .. great tip... I initially used DAX for running total in a Power BI visual but due to huge data size, matrix visual was taking a long time to refresh whenever a filter is applied. So I thought to perform the calculation within Power Query to speed-up the report visual. I was able to create the column with running totals in Power Query, however, I have two additional columns; product and month. I need Jan-Dec running totals for each product. Is it possible using this method?
Please see this tutorial: www.myonlinetraininghub.com/grouped-running-totals-in-power-query
You lost me right at the end but I love it. Thank you. Would you be able to do a video where the RT restarts with one or multiple criteria’s?
Hopefully when you download the file you can inspect it yourself and understand it better. Not sure what you mean by 'where the RT restarts with one or multiple criteria'...isn't that what this is doing i.e. restarting at each group? Perhaps you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Mynda, I think edsta714 is referring to a running total "at each change of XXX". I understand your method completely and how to apply it to grouping, but I'm guessing edsta714 is trying to understand how this would work (or could be made to work) if you had two columns in your data rather than just a value column. To achieve this:
Area Value RT
East 123 123
East 123 246
East 100 346
West 123 123
West 100 223
etc.
I think that's the question anyway!
@myonlinetraininghub Hi Mynda, I'm have a similar thought as Rico Illustrated... can this be used to create a "running sub-total" if we have multiple sub groups in a data set?
Thanks works brilliantly. For my data I want to have another running total in the same table based of another column but still linked by Country. E.g add a column = Value2 and have a RT for Value2 alongside the RT for Value. How do I fit that into the code?
Actually figured that out, was relatively easy.
Great to hear!
I like using power query over dax a lot becuase while it makes the updating and adding data take longer it makes the dashboard faster for the users.
If you need the running total in the source data it makes sense to use Power Query 👍
@@MyOnlineTrainingHub I also do a lot of comparing to last period and I find it easier to use two index columns and merge the table to itself and use calculated columns to do the math in power query.
Hi Mynda
Tks for the info.
I encounter issue with power query
I wanna set auto refresh on both pivot table and power query
The data used in the pivot table is the output of power query.
After i set an VBA workbook.refreshall on the source data sheet, and disable the background refresh, everytime I open the worksheet, it just continously non stop refreshing the workbook which I forced me to end the application.
I wonder if I did anything wrong?
Thanks in advance
Probably :-) please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Nice 👍
Thank you!
at ~11:08 you show that the query still executes 'instantly' after increasing the filtered row count from 5,000 to 100,000. My understanding was that the query doesn't actually execute until you load the data; and that's when you can compare performance. Everything you see in the PQ editor is being performed on a preview of the dataset. Am I wrong in this belief and actually the query is being performed on the whole dataset live in the PQ editor?
Thanks, valuable video. After watching this video, I got an question. Is it possible to calculate cumulative percentage in power query?
Glad you liked this video! You can calculate cumulative percentage in the same way. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great tutorial! How do you apply this if you have sub-categories?
Thanks, Arnie. See this tutorial for grouped data: www.myonlinetraininghub.com/numbering-grouped-data-power-query
Hi Mynda, Thank you so much for the video. It's super useful :)
Small help required !
I tried to calculate the running total based on the same logic explained. I was able to make my way through all the steps but on the RT step it returns me a NULL value for all rows. Not sure why !
*Just a note here - I have total three columns. Any thoughts ?
Glad it was helpful, Manoj! As for the running total issue, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Ma'm.
Can you please make a detailed video on Excel Header and Footers?
It would be great if that includes using VB for creating dynamic headers and footers.
I have a specific query regarding above task.
I want to put an image in the header. Image should be existing in a worksheet so that sending the Excel file through mails doesn't mess up the header image.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Excellent, thank you! One question, though: at 4:48 you are saying "[Index] list...". Why "list"? why isn't it just the value in the [Index] column of the current row?
Hi Michael, a column in a table is technically called a list. So when I'm referring to the Index list it's the same as saying the Index column. Hope that clarifies things. I can see it can be confusing when terms appear to be mixed.
gREAT!!! Supossed we have another column dividing the Value column..... How can I do this exact path but with categorical group?
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda. Thanks for this excellent tutorial. One issue I have since today (might have been from the weekend), my List.Buffer function now returns my list in a different order than the original column. This was working for the past month. I can't understand why it's failing now. Has something changed on Power BI? (it's in a dataflow in case that helps)
That's odd, Laurent! I suspect it's at the dataflow level and List.Buffer is just buffering the list as it receives it.
Thanks @@MyOnlineTrainingHub not sure how or why, I then experienced the exact same issue with Power BI Desktop. In the end what fixed it for me was to Table.Buffer() before trying to List.Buffer(). In case it helps others...
Great to see this work, but I can't help wondering why you wouldn't just load the data into a pivot table and add a running total column that way, which is significantly easier and doesn't have any of the speed or memory issues you talked about early on in the video. Maybe I'm missing something - please explain! Thank you.
Hi Harry, I agree, PivotTables is the right place for running totals, but sometimes people want them in Power Query as they are later used in other calculations in Power Query before loading the data.
Great video and very useful. I think maybe @edsta714 means can you do running totals by category - ie by the value of another column (eg by product or geography or month). I realise you can do this in Pivot Table or DAX but if for some reason you want it in the Power Query step it would be useful
Thanks, Andrew! Re @edsta714's comment, you mean by nested groups. Yes, you could rank by nested groups by performing another grouping of the data before adding the index column.
@@MyOnlineTrainingHub Could you do a video on this? It would be really useful!
Good👍👍
Thank you!
hi thank you for knowlodge please show how create a trading plane dashboard like a pro ?
Thanks, Ahmad! I don't know what 'trading plane' is, sorry.
how do you do if you want to sum january to december and then a new year start again cumulative?
I'd do this in a PivotTable.
Hi Mynda...what if I need to calculate RT on more than one "value" columns from one table?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, great video, I'll probably have to watch it 100 times, hopefully something will catch on in the end....
What if I want to make a running total for 2 or 3 columns? I messed up...
Glad you liked it, Eitan! Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Cannot we wrap up the source with List.Buffer and then apply the List.FristN and List.Sum?
List.Sum(List.FirstN(List.Buffer(Source[Amount],[Index]))) something like this?
If you put that after the 'each' keyword in the Table.AddColumn function then it will likely be even slower, because for every row in the table it will do the whole operation, including re-buffering the list.
Really interesting thanks. I have a different problem: I need to check the sum of each employee's working hours per month, against a monthly contracted number of hours. So I require a running total (and monthly reset) of a dynamic number of employees. This is because I'm doing "point in time" analysis, not just looking at monthly aggregates. I'm dimly aware that this might require use of a date (calendar) file, in order to "move along". Do you cover that sort of thing anywhere, or could you point me in a direction?
I'd probably just use a PivotTable running total via the right click > show values as > running total in > 'days'. If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub thanks I'll try that and join your forum!
Hi Mynda & Phil,
Thanks for the excellent article & video - performance is surprisingly good. On some days my brain does struggle with the way Power Query utilises the each keyword and underscore conventions in functions like List.Generate. Below is my much more verbose version which I find a little easier to understand in case it is of interest to anyone..
(Values as list)=>
let
BufferedValues = List.Buffer(Values),
fn_Seed = () =>[Counter=0, RunningTotal=BufferedValues{0}],
fn_ContinueWhileTrue = (CurrentRecord)=> CurrentRecord[Counter]
let
NextRecord = [
Counter = CurrentRecord[Counter] + 1,
RunningTotal = CurrentRecord[RunningTotal] + BufferedValues{Counter}
]
in
NextRecord,
fn_ReturnValue = (CurrentRecord)=>CurrentRecord[RunningTotal],
Output = List.Generate(
fn_Seed,
fn_ContinueWhileTrue,
fn_GenerateNextValue,
fn_ReturnValue
)
in
Output
Thanks for sharing, Charl!
Hi Mynda. I know this is an old video but I've only just come across it in trying to find a good way to add any kind of window column to a table... great stuff! Thank you.
The thought occurs - given there's no consideration for referencing an index column of the original table, could you use the List.FirstN function inside List.Generate, and reference the current iteration using _? Like:
List.Generate(
() => 1,
each _
Good question. I don't know off the top of my head. I'd have to test it.
@@MyOnlineTrainingHub Tested it on a table with ~5m rows and it is noticeably slower (although still faster than using an index column).
I guess using List.FirstN means every item in the list (except the last) would be evaluated more than once - the first item would get evaluated 5 million times... whereas your way evaluates each item only once.
Thanks for showing how to do this! 👍
What is the advantage of doing in power query instead of dax?. Great video BTW!
Thanks, Daniel! If you want a rank that respects filter context then I'd use DAX. If you want the rank to remain unchanged, I'd use Power Query because it's typically more efficient for PQ to add calculated columns that it is to do this in PP.
Great though there is hardly only one category in the data, so let's say there is a 'product' category and its sales per day, how do you reset the count for each product category or each date category (weeks or months)?
I'd probably use a PivotTable, but if you must use Power Query, see this tutorial on grouped running totals: www.myonlinetraininghub.com/grouped-running-totals-in-power-query
Thanks 🙏 , but if we need to be like sumifs Accumulated balance depends on multiple criteria
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How to reset running total every year if data is year - monthly based?
Here's a tutorial on grouped running totals: www.myonlinetraininghub.com/grouped-running-totals-in-power-query
Hi, I try to use the same formula, but it just keep saying "Added Indedx" wasnt recognised.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Gosh - that's very advanced. But I don't know anything about that programming language you were using. What is that language? Is it part of Power Query? I think I got lost along the way somewhere.
Many thanks.
Hi Ian, Power Query uses the M language specific to Power Query.
@@MyOnlineTrainingHub Thanks, Mynda.
Bit advanced for me but very good
Cheers, Graham!
Jeez, not sure I can say I'm an advanced excel user anymore. I'm quite behind the times on excel tools.
Glad you learned something new, Boris :-)
What about by subgroups that long 5000 rows
Not sure what you’re referring to.
@@MyOnlineTrainingHub my data set has groups and subgroups such as Films, then action, horror etc how to do running totals for that kind of situation
I would do this in a PivotTable. Not sure why you'd want this in your raw data.
I get cyclic reference error
Must be something different in the way you've written your query.
Wow, you lost me, but this was really great.
Hopefully the Excel file will make it clearer.
Omg this is too advanced and tough.
Yeah, some things require a bit more work than they should. If you need help you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
what is #"Added Index" ?
That is the name of a step. It's usually the previous step, but doesn't have to be.
@@MyOnlineTrainingHub thanks for the reply!
A bit too advanced for me but it was amazing nonetheless.
Thanks, Abdul!
You' re nice and your presentation nicer??
Thank you! 😃
Microsoft must hate their users to necessitate anything like this. They must want users to use Python or SAS. Excel already has a running total feature in their pivot tables. So, I'd think the logic would be have been made more accessible here.
I think you nailed it...they expect you to perform running totals in PivotTabes, not in the source data, which is effectively what Power Query is designed to get. But us Excel users always want what we can't have :-)
So that’s why I want what I don’t have ... b/c I’m an Excel user!
I both agree and disagree. I think they are perhaps trying to separate the transformation and calculation layers here and steering people away from PQ for this type of thing. It makes sense from the perspective of building these applications, and 99/100 times you would probably be better served doing this. As Mynda mentions in another comment, you might want to perform the calculation for use elsewhere in the transformation piece. I'd like to see them move toward a unified experience between PQ and Power Pivot (like in power bi), which I think would help us visualise the cut off between transformation and creating measures.
It's definitely a function I'll be copying for future use, thanks Mynda!
To Rico’s point: I had a huge aha! moment in my PQ, PP, DA growth when I realized that I was trying to do way too much in PQ. Part of that was learning: I wanted to see can be done in PQ, so I tried everything. But, because we can does not mean we should. Since aha!, I restrict PQ to ETL & generally avoid calculations, which I do in the workbook after load. From time to time, there’s an exception, but that’s my general rule. Life is better since then.
My brain is full of carbon dioxide now
If you nest the 'buffer' within LIst.FirstN will it work the same? so ;
List.FirstN( List.Buffer(#"Added Index"[Units] ) ,[Index] ) ) , rather than,
List.FirstN ( Bufferlist,[Index] ) ) , I can't see any reason why there should be a performacne issue, but i've been wrong before. 🙄🙄
Hi William, yes that will work the same.
@@PhilipTreacy0 Thanks for the response, I don't know if you've done similar to generate but with accumulate; so
Table.AddColumn(#"Added Index", "RTC", each List.Accumulate(#"Added Index" [Unit] , {0} , (A, B)=>
A & { List.Last(A) + B } ) { [Index] } )
Must admit tried diescting and getting down to whats going on such as why the seed needs ot be a list and still not sure. Might make a fun video, keep up the good work.