Power Query Running Totals - The Right Way! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Mark
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?
reply
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?
reply
Williams
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
reply
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
reply
Alex
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?
reply
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?
reply
Manoj
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 ?
reply
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 ?
reply
Laurent
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)
reply
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)
reply
Harry
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.
reply
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.
reply
Andrew
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
reply
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
reply
Geert
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!
reply
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!
reply
elTurkoCharro
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!
reply
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!
reply
Gordon
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?
reply
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?
reply
Add a review, comment
Other channel videos
