Great lesson, you must have a full production team putting your videos together, I've never seen so much animation in a tutorial video, it's like going to a party. Thanks for the Get Transform, Lynda.com lessons. Your the first one to teach me Power Query, and I just passed my 70-779 certification!!
JR you're so welcome! I'm glad you liked the Lynda courses. The video production is all me. It takes a lot of time and I enjoy it. It's good to know that others find value in it. 👍👍👍
Oz, thank you for sharing all the knowledge and simplify this topic. If your data is processed in Excel I would like to suggest another solution: 1: import an Excel table in PQ. 2: just give the table back to Excel (green table). You can add a formula on the right: SUM($C$5:C5) copy down. (Assuming in D5 is the first running total). If you add new data to the excel table and click refresh and enjoy this solution as well....:)
That's a good idea....but power query is generally used for handling large sets of data...and for such data it will be difficult to use functions back into excel as even running a small fiction like sumifs will be too much memory consuming.
Much easier, this one! On the other hand, the other video taught me lots of good stuff. Thank you, Oz and all the other awesome "pirates" riding Ubers! :-D
It really works fine thanks I was desperately looking for it. But loading is taking way too much time, too much slow. My table has nearly 50 Thousand lines. Is there a solution for this? Till there, I am doing it just by the table with simple excel formulas. Working fine too, and faster. But the data doesnt belong to the data model this way. Thanks again!
Hi! Great tip for running totals. It's simple and does not require much work. Quick question though. What happens if I want to add running subtotals by a certain parameter. Let's say that your file has an additional column for "Store". Where you have Store A, Store B, Store C, etc. And you want to have the running total by store. So, every time a new Store comes in the list, it resets the running total to 0. How can I do that? thanks again!
Very helpful! can you think of a way of doing a double running total for two different columns? for example visits and profit of each day in your example
Great video. If we had the total entries by gate (e.g. by extra column GATE Numb), and I want to have the running totals by gate. Is it possible to do by this way?
I need a running total of products stock by time of warehousing. Table has many products in different warehouses. I would like to calculate a running total by product and by warehouse. So the columns of the table would be: Date of warehousing, warehouse, product, amount, running total of amount by product and warehouse.
GOTCHA! This is interesting. I don't have an immediate solution, though. My initial thought is to pull the different pieces apart, do the running totals, then put them back together. But that would get really messy if you have more than, say, 3, running totals. A quick search shows a lot of people have this question and they opt to handle it in PowerPivot. This would be pretty easy in native Excel, but I'm not envisioning a clean way of doing it in Powery Query. But you got me thinking.
I have to add this. It has to be in power query because there is a lot of data and DAX would not handle it. Also, there are a lot more than 3 running totals. I think about 10 different warehouses. And more than 1000 different products. I have found this example: onedrive.live.com/view.aspx?cid=4fa287bbc10ec562&page=view&resid=4FA287BBC10EC562!12099&parId=4FA287BBC10EC562!398&app=Excel But I could not manage to make it work on my data.
Excel On Fire the video you showed displays for a way of calculating a running total from individual increment values. However, sometimes you might have the running total and you are trying to determine the original increments by month. A reverse running total essentially is what I’m asking. From a running total, which is the result of what you’ve done, if you already have these values and want to work backwards to obtain the incremental values. :) Any ideas on this?
Sir have tried this =List.Sum(List.FirstN(#"Added Index"[visitors],[Index])) but all from you, your videos just change my idea.we required more videos at least once in a week.
Oz, does the sorted column in the PQ adjust to all source data sorts? Meaning, if the source data is sorted a different way, do you have to match that sort type in the PQ each time or does the sort step in PQ adjust to any sort done in the source data? Is that clear as mud? LOL
The sort in the query puts things in order so that the running total is meaningful. The source data could be completely out of order. Does that answer your question?
How can i make a running total by a segment? Lets say you have 2 products and you want a running total to be calculated individuality for each product every day?
Thank you for this! This seems to take a lot more mental energy for Excel to do this in PQ vs. doing a running total in a Pivot Table, or even doing a sum with an absolute value at the beginning of the range. Would you say that it is a best practice NOT to use PQ for running totals if you can avoid it? - Signed, your new Work Wife
AH! My new Work Wife! HOTDOG! 👑 I agree with you. Running totals are complex in Power Query when compared to staying in Excel and using SUMIFS or a pivot table. I know I'd try to avoid it in PQ, but we know that it's possible in case you're forced to be in PQ and need something fully automated.
It looks like one has to be very careful about data formats. Column in General format adds up just fine in stand-alone query, but seems to fail if you put it in a function. It works fine in both places if I format column as Decimal or Integer.
Amazing video. Saw so many blog posts but your solution seems the best and the easiest.
Thank you!
consistently amazed and grateful for all the intelligent people out there willing to share their knowledge with others!
🤗
Great lesson, you must have a full production team putting your videos together, I've never seen so much animation in a tutorial video, it's like going to a party. Thanks for the Get Transform, Lynda.com lessons. Your the first one to teach me Power Query, and I just passed my 70-779 certification!!
JR you're so welcome! I'm glad you liked the Lynda courses.
The video production is all me. It takes a lot of time and I enjoy it. It's good to know that others find value in it. 👍👍👍
JR congrats on passing your certification! 🎉🎉🎉🎉💃🕺💃🕺💃🕺
This saved me so much time. Very helpful.
HOTDOG! 🌭🌭🌭
Oz, thank you for sharing all the knowledge and simplify this topic. If your data is processed in Excel I would like to suggest another solution: 1: import an Excel table in PQ. 2: just give the table back to Excel (green table). You can add a formula on the right: SUM($C$5:C5) copy down. (Assuming in D5 is the first running total). If you add new data to the excel table and click refresh and enjoy this solution as well....:)
I like that strategy! YEAH! I can see it.
That's a good idea....but power query is generally used for handling large sets of data...and for such data it will be difficult to use functions back into excel as even running a small fiction like sumifs will be too much memory consuming.
Much easier, this one! On the other hand, the other video taught me lots of good stuff. Thank you, Oz and all the other awesome "pirates" riding Ubers! :-D
Thank YOU!
Uber Pirates. I like that. I like that.
Thanks Oz, very helpful and explained clearly and step by step
HOTDOG! 🌭🌭🌭
Thanks, Oz. I love the collaboration. Four heads are better than one LOL. Stay On Fiya!
YES YES! I love the collaboration. It keeps the fire from going out. 😊
Fire and Running Totals are fun !!!! Thanks : )
... and BeeGees!
1970s disco : ) They did not have Uber back then, or TH-cam!!!
It's a new type of experience: cross-generation mashup 🔥🔥🔥🔥
Perfect - solved my problem. Couldn't ask for anything else.
fantastic!
Sir! Thanks to you, my excel is on fire!!! Thanks 🔥🔥🔥🔥
FRANK!
Excel! Excel
Excel is on fire!
We don't need to water,
let the mofo burn ... 🔥🔥🔥
@@OzduSoleilDATA 🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥
Even more magic than the previous one!!! Have a good day in Oz Land!!!!
🎆🎆🎆🎆🎆
It really works fine thanks I was desperately looking for it. But loading is taking way too much time, too much slow. My table has nearly 50 Thousand lines. Is there a solution for this? Till there, I am doing it just by the table with simple excel formulas. Working fine too, and faster. But the data doesnt belong to the data model this way. Thanks again!
Thanks. Small (syntactic) enhancement suggestion: replace List.Range with List.FirstN. You can skip de 0.
Well done!
Thank you!!
Great Video, Thanx!!
You are VERY welcome!
Great video.
Thank you!
Hi Oz,
Thanks to everyone! ;-) Nested M fonction, that's how I like it!
Yes yes! Much cleaner. I like it that way, too.
Hi! Great tip for running totals. It's simple and does not require much work.
Quick question though. What happens if I want to add running subtotals by a certain parameter. Let's say that your file has an additional column for "Store". Where you have Store A, Store B, Store C, etc.
And you want to have the running total by store. So, every time a new Store comes in the list, it resets the running total to 0.
How can I do that?
thanks again!
Very helpful! can you think of a way of doing a double running total for two different columns? for example visits and profit of each day in your example
That's a tough one. My friend Faraz solved this challenge here: th-cam.com/video/uvOX-Oy-Zdk/w-d-xo.html
Will this work with few millions of rows? If List function stores values in memory then we might have problems, wouldn't we?
My understanding is that this should work on millions of rows. One limitation is that you can't load millions of rows onto a worksheet.
Great video.
If we had the total entries by gate (e.g. by extra column GATE Numb), and I want to have the running totals by gate. Is it possible to do by this way?
Thanks for this, greate video! Is there a way to do this by categories? Additional column category -> running totals by category.
I'm sure there's a way. That'd take a bit of research. What's an example you'd have in mind?
I need a running total of products stock by time of warehousing. Table has many products in different warehouses. I would like to calculate a running total by product and by warehouse. So the columns of the table would be: Date of warehousing, warehouse, product, amount, running total of amount by product and warehouse.
GOTCHA!
This is interesting. I don't have an immediate solution, though. My initial thought is to pull the different pieces apart, do the running totals, then put them back together. But that would get really messy if you have more than, say, 3, running totals.
A quick search shows a lot of people have this question and they opt to handle it in PowerPivot. This would be pretty easy in native Excel, but I'm not envisioning a clean way of doing it in Powery Query. But you got me thinking.
I have to add this. It has to be in power query because there is a lot of data and DAX would not handle it. Also, there are a lot more than 3 running totals. I think about 10 different warehouses. And more than 1000 different products. I have found this example: onedrive.live.com/view.aspx?cid=4fa287bbc10ec562&page=view&resid=4FA287BBC10EC562!12099&parId=4FA287BBC10EC562!398&app=Excel
But I could not manage to make it work on my data.
Yeah. That method in OneDrive is a kind of "pull it apart and put it back together" solution. Not suitable for your need.
Hi There, thank you for this! How do you figure out the opposite? How do you figure out the period value if you have a cumulative value for every day?
I'm not sure what you're asking.
Excel On Fire the video you showed displays for a way of calculating a running total from individual increment values. However, sometimes you might have the running total and you are trying to determine the original increments by month. A reverse running total essentially is what I’m asking.
From a running total, which is the result of what you’ve done, if you already have these values and want to work backwards to obtain the incremental values. :) Any ideas on this?
Sir have tried this =List.Sum(List.FirstN(#"Added Index"[visitors],[Index])) but all from you, your videos just change my idea.we required more videos at least once in a week.
I'm trying. Once-a-week is hard. But I'm trying. ⚔
Super fun to add running totals with PQ...
Thanks very very impress, thanks Oz.
Very very you're welcome, Wess!
I would have cussed, but out of politeness, I won't. Thank you. This is useful.
🤣🤣🤣
Excellent 👍
🔥🔥🔥
How to do running totals in monthly fashion (MTD) in Power Query? also category wise (Sales, Cost of Sales etc)?
Oz, does the sorted column in the PQ adjust to all source data sorts? Meaning, if the source data is sorted a different way, do you have to match that sort type in the PQ each time or does the sort step in PQ adjust to any sort done in the source data? Is that clear as mud? LOL
The sort in the query puts things in order so that the running total is meaningful. The source data could be completely out of order. Does that answer your question?
Yessir! Thank you!
How can i make a running total by a segment? Lets say you have 2 products and you want a running total to be calculated individuality for each product every day?
Bon bagay!
mèsi anpil
Hey Oz!! Wiz the code keeps loading and makes the query very very damn slow!! Any comments on this please..
I have no idea what that could be.
Thank you for this! This seems to take a lot more mental energy for Excel to do this in PQ vs. doing a running total in a Pivot Table, or even doing a sum with an absolute value at the beginning of the range. Would you say that it is a best practice NOT to use PQ for running totals if you can avoid it? - Signed, your new Work Wife
AH! My new Work Wife! HOTDOG!
👑
I agree with you. Running totals are complex in Power Query when compared to staying in Excel and using SUMIFS or a pivot table. I know I'd try to avoid it in PQ, but we know that it's possible in case you're forced to be in PQ and need something fully automated.
nice
Thanks! 👍
Personally I think I rock that party hat #MVPNewbie 🤣
You do!!!! You do!! 🔥🔥🔥🔥🔥🔥 #MVPNewbie
Hello Wyn: congratulations for being MVP, I like your enthusiasm about Power BI. !!
Hi , thanks Bart
I have tried this technique in a function and strangely enough it does not work there.
I haven't tried this as a function
It looks like one has to be very careful about data formats. Column in General format adds up just fine in stand-alone query, but seems to fail if you put it in a function. It works fine in both places if I format column as Decimal or Integer.
No need to sort - List.Sum(List.FirstN(#"Added Index"[Visits],[Index])
How about just 2 steps instead?
Source = #"Base Data"
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[Amount],[Index])))
Just 2 steps? Interesting!