Fast Running Totals in Power Query (Complete Guide)
Вставка
- Опубліковано 4 сер 2024
- Running totals in Power Query can be fast, if you know how. This video shows different methods and analyzes why some perform better than others.
WRITTEN ARTICLE:
gorilla.bi/power-query/runnin...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
CONTENTS
00:00 Introduction
01:27 List.FirstN
05:02 List.Range
06:06 Performance Problems
10:47 List.Generate
17:33 Combine Running Total with Table
23:41 List.Accumulate
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ua-cam.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#RunningTotal #powerquery #bigorilla
Want to read the written article? You can find it here: gorilla.bi/power-query/running-total/
And in case you want to take it to the next level, this article shows how to compute the running total by group: gorilla.bi/power-query/running-total-by-category/
Enjoy Power Query!
19:55 w8... W8.... WHAT!?!?! I mean Wow, I am amazed and baffeled and speachles I was fucusing on combaning list using other PQ methods but you can actualy use the & symbol to combine lists? Wow!
Thank you so so so much :) Wow...
This is crazy. For years I've been duplicating queries, removing columns, doing GroupBy, and Sum, and then pulling the totals back to my original dataset with a merge. I've created so many unnecessary tables. This is a godsend for me. Thanks a million!
Thanks Scott, really happy to hear it worked for you!
At the end of the video,the method to deal with "null" is so great, it is a perfect solution.
Wonderful video Rick! I went over your blogs on PQ, Excellent content! Thank you very much for that effort and very simple explanation.
Incredible video. I learned List.Generate recently. Didn't know I could iterate like I was writing a loop in python.
List.Generate is way more powerful than I thought.
Great videos. Keep ‘em’ coming 👍
This was excellent thanks Rick. I have learned so much from you so far already. Please keep on creating this excellent content!
Thank you Renier. More is coming, and soon 🫶
Excellent clear video, thank you!
Great video Rick, very informative and easy to follow, even for a beginner like me, thanks
Espectacular esta lección de Power Query, gracias !!!
Hey Rick! thank you so much for sharing this info, helped me a lot with a project I was stuck on for a few days. Kind regards!
So happy to hear that !
I'm a beginner with PQ but was able to follow you well. Great and clear explanation. You have a fan :) thank you.
So glad to hear that!
Thank you for this video.. it really beneficial to me in constructing accounting general ledger.. thank you again
Absolutely fantastic - Clear in thought, explanation, voice, demo & samples, Thanks a ton
Highly Appreciateed Ranjith!
@@BIGorilla it worked perfectly for me, but for large dataset it's stuck @ 432mb while loading and not going beyond
Thank you so much for creating this amazing Video on running total. #1 tutoring on running total.
glad you like it!
What a great video. I think this will really help time series calculations in my dashboards
I would mostly recommend DAX for time intelligence, including running totals. There are scenarios however, where you may already need it in power query.
Thank you so much BI Gorilla, That's help me a lot!
Thanks for pointing out that null-trap and how to avoid it. Great stuff! :-)
That null trap is tricky. Glad you found it useful 👌
& i love the List.Buffer thing. Hats Off!
Wooooooo, thanks jerome. Glad that is working for you!
Thank you for the clear explanation ! :)
Thank you!
Thank you very much for sharing it.
It's a pleasure!
Great great content! Congratulations
Thank you!
This video was great cleared all my concepts.Can you share a video on consolidating files with changing column headers especially when there are many columns.
excellent video & well explained as always
Thank you very much William, you were quick on watching this one. Next week I'll release an interesting follow up video. It uses the fast logic, but shows how to apply it to groups.
Superb!, Thanks for sharing!
Thank you jose! 🙏
Brilliant!!!
Appreciate it :)
Very Impressive!
Thanks!
Many thanks for such a great content.
From a rookie view, wouldn't be easier and "faster" to create the running total as a measure with DAX (Power BI or Power Pivot)?
In any case, I've learnt a lot.
Thanks.
Great mate thanks for posting 👍. Is there a time saving even for small data sets? ….Say sub 50 rows.
Great series of videos, I am currently trying to see if I can take some of these principles to improve my Grouped Rolling Total Values calculation speed. Rolling total is slightly different to the running total described in your video, as I am interested in previous 3,6,9,12 months etc. Currently I am using List.Sum with Greater Than and Less Than conditions, but with a couple thousand rows this takes very long time (1+hr to calculate). Could not find existing Power Query video on this topic, but if you have one, please point me in that direction.
Please have a look at : gorilla.bi/power-query/running-total-by-category/
What a great video!
Tons of valuable content!
Saved in the learning playlist!!
Thanks Abdoul! ❤
Wao! 👏
I am doing a running total for inventory forecasting. Each location has a Max inventory allowed and Min inventory allowed. How can I do a Running Total for each category, where each category has its own Min/Max limit? For example, inventory cannot exceed 2200 and cannot be below 200 for location "A", whereas location "B" cannot exceed 500 and cannot be below 50. This Min/Max is listed in another column that can be referenced for each category/group. I have the running totals working for each group but cannot seem to figure out how to incorporate min/max limits. Can you help?
Thank you for your time and solution to the problem that you expose. Greetings from Chile.
My pleasure, it can be overwhelming without the right tools. Glad you like it!
Hi Rick, this is a great way of doing it. I was just wondering if you have multiple products and want to see the running total per product, how would you calculate that? Can you group it dynamically?
Hugh, good question. The answer is, yes you can. I’m releasing the video on it next week. But if you can’t wait, here’s the article on it: gorilla.bi/power-query/running-total-by-category/
That magic index column.🤔
You bet, the column you never know you needed this much 🙌🏻
Hi Rick!
Super usefull, thank you!
P.S. What about the cherry on top - code encapsulated into function like AddRunningSum(TableName, inColumnName, outColumnName)
?
True Denis. You will find it in next week’s video. But the article here shows you how to do it :
gorilla.bi/power-query/running-total-by-category/
Really great video, I found Generate hard, and still haven't been able to get the data type to work,
I get the error " can't convert list to type", any chance of doing a bit more on the accer method, I've been using it for ages without really understanding it, this is a bit different to your method;
List.Accumulate( Source [ Unit], {0 } , (A, B )=> A & {List.Last(A) + B } )
I've not been able to isolate the ampersand to see what it's doing. Lastly and alternative to your ingenious method of joining Source to list , just add column with an Index as place holder . More curly brackets.
Here is my take on this, using an index column to merge the two lists:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}}),
Number = #"Changed Type"[Number],
Custom1 = List.Generate(
()=> [RT = Number{0}, Counter = 0],
each [Counter] < List.Count(Number),
each [RT = [RT] + Number{[Counter]+1}, Counter = [Counter]+1],
each [RT]
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Running Total"}, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Number", each Number{[Index]}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Number", "Running Total"})
in
#"Removed Other Columns"
That's a great approach!
Hey can you please make a video on how to calculate running count via calculated column measure in powerbi
Thanks for the tips, but on my part the Consolidation step of the List.Generate solution is extremely slow, even though I'm working with a small subset of data (89 rows). I definitely need a workaround for that!
List.Generate is a very efficient function. It's likely you included some other steps that interfere, or have not included List.Buffer
Explanation is good, however, the need for simple lany. 😊
Hi. Can you help me with the following. I want to to create a column that looks something like this. IF (Column name) >=20230601 but
Great explanation and guidance,
I have applied your step everything look great, but when I load the table to sheet the Running Total Column showing Blank with out any value where the value already existing in the query, can you please help my in this issue.
Manty Thanks
Could you describe your issue a bit more? When does this happen? Do you have null values in your values column?
If so, make sure you use List.Sum. It also works with null values. You can copy the setup here:
gorilla.bi/power-query/running-total/#running-total-with-list-generate
I’ll need more info to pinpoint the issue.
Thanks, Rick
💯👍
Does this work even if the columns I'm dealing with are calculated columns?
These are not calculated columns, since they are in power query. Calculated columns are only in the front end in Power BI
This all should be doable via gui. That's one thing missing in Power Query.
Let's hope there will be an easier way in the future. I agree this should be an UI functionality.
great formulas but you explain them badly
Thanks - yes this video is an excellent one where I explain each method. If you don't get it, watch it again. Cheers
Great content.. I recently switched to list.generare. And I do see performance improvement. But my next challenge in power query is to do FIFO tables, where for each material there is sales and purchases. Doable but very challenging. If you run out of content, please consider how to do FIFO using power query.
Great suggestion Sumanth. I am not too familiar with the underlying calculations for FIFO, although I have worked with the accounting principles.
Can't commit to this right now, but it may be fun to delve into later. Thanks for suggesting 🙌🏻🙌🏻
@@BIGorilla Thank you.. Great content so far.. I am also attaching a link to the FIFO sample calculation image... I see few solutions based on DAX..But It's definitely doable in power query :)
List.Accumulate(
L_Buffer,
[ytd={},extract=0],
(sortie,entree)=>
[
extract = sortie[extract] + if entree = null then 0 else entree,
ytd = sortie[ytd] & {extract}
]
)[ytd]