The Secret to List.Accumulate in Power Query
ฝัง
- เผยแพร่เมื่อ 8 ก.ย. 2024
- List.Accumulate is an extremely powerful function in Power Query, watch this video to understand exactly how it works and how to use this List function.
Please download the practice file here: docs.google.co...
✅ Please see link to videos mentioned in this video:
🎦 How to Calculate Running Totals in Power Query - • How to Calculate Runni...
🎦 Power Query Advanced Editor - What Is It and How to Use It? - • Getting Started with t...
✅ If you would like to connect on LinkedIn:
🤝 www.linkedin.com/in/missmicrosoft
✅ If you would like to support the channel by buying me a coffee, (it's really quick and easy), please use this link:
☕www.buymeacoff...
✅ For more videos please subscribe:
🔔 / @missmicrosoft
✅ Please also check out my Excel course on Udemy, please use this link:
📖 www.udemy.com/...
Be among the first 100 to jump on board "The Query Editor" newsletter 🚀. It's packed with super-actionable tips and insights. Why should you be excited? 🌟 Because the first 100 subscribers will receive an exclusive 50% discount on my upcoming mini-course, set to drop early next year (or perhaps even sooner)! 🤞Just hit this link to subscribe thequeryeditor.beehiiv.com/subscribe
🚀🔥
This is really the BEST video which is available on youtube about List.Accomulate . Full of explanation.. Well Done! and waiting for the next video...👏👏👍
Thank you for your kind words Zahoor! Much appreciated 😊
Hi @Zahoor, here is the next video, enjoy 😊: th-cam.com/video/RyShRDv92EQ/w-d-xo.html
Actually, the best way to understand how List.Accumulate() really works is treating 'state' as the object you're constructing and 'current' as the current item in the list passed in the function's 1st argument. 'start' is the initial object which from you'll begin to construct your desired object. For example, I have a list of names {"Bob Brown", "Ashley Hall", "Josh Parker", "Wesley Graham"} and I want to create a list of surnames from that. Using List.Accumulate(), we could get:
let
Source = {"Bob Brown", "Ashley Hall", "Josh Parker", "Wesley Graham"}
SurnamesList = List.Accumulate(
Source,
{},
(state as list, current as text) as list =>
let
SplittedName = Text.Split(current, " "), // List with name and surname
Surname = SplittedName{1},
SurnameAddedToState = List.InsertRange(state, List.Count(state), {Surname})
in
SurnameAddedToState
)
in
SurnamesList
In this code, I stressed the 'state' is a list and the 'current' is a text, because I want to construct a list of surnames based on the names in the Source list, starting from a empty list {}. You can name them as you please, but knowing it'll be always 2 arguments to the Accumulator function: the first being the object you're constructing whereas the second being the object from what you're manipulating to construct your desired object. The iterations are just as the following:
1st iteration: state = {}, current = "Bob Brown";
2nd iteration: state = {"Brown"}, current = "Ashley Hall";
3rd iteration: state = {"Brown", "Hall"}, current = "Josh Parker";
4th iteration: state = {"Brown", "Hall", "Parker"}, current = "Wesley Graham"
Final Result = {"Brown", "Hall", "Parker", "Graham"}
I hope I helped someone with this explanation.
Sorry for my bad English btw
Thank you this is well explained!
For the closing column (I refer to Excel file you showed at the beginning), an alternative solution to calculate accumulated value, or the running total is following formula, starting from row 2:
=SUM($B$2,B2). If you drag and copy formula down, you will get the values from the Closing column. I know it might be too condensed for the concept you wanted to explain, but it is an alternative. Also, we love your English accent, it is simply delicious .
Why haven't I found this amazing channel before? Where have you been all my life?
Thank you for your kind words! Please keep watching and if you would like to subscribe to my newsletter that would be wonderful as well! thequeryeditor.beehiiv.com/subscribe
One of the best intro to List.Accumate. Cristal clear!
Thank you Roberto, I really appreciate the kind words!
That four column table to help visualize things is brilliant!
Really glad to hear it was helpful Brian!
Best illustration of how List.Accumulate works👍👍
Thank you so much Kebin! Glad you enjoyed the video :)
Best List.Accumulate explanation in TH-cam!!
Thank you so much, I really appreciate that!
Big fan of your work. You explain list.accumulate gloriously!
Thank you very much for your kind words, I really appreciate it!
Very good! I got that to work but now I have to add logic so that only this only applies to the group of site ids and restarted for each site id. Not sure how to do that yet but will figure it out
Wow, so many nuggets of gold in this video. (converting a column to a list by referencing the previous step was magic to me).
This is the first time I've seen a video from your channel, and I am now a subscriber.
Thank you, I’m so glad the video was helpful! And thank you so much for subscribing 😊.
If you liked the conversion of the column to a list, maybe check out my Power Query Pro series, I show you more helpful tricks: th-cam.com/video/kqHMP0OzzBo/w-d-xo.html
Why is your channel not bigger. This video is 🔥🔥🔥🔥
This is one of the best! Great video!!!
Thank you Kenneth, really appreciate it!
This is awesome!
I was really looking forward to watching this video
I’m really glad to hear! Thank you for watching 😊
Awesome explanation...
Thanks a lot 😊
Very nice! Thanks for posting. Thumbs up!!
You’re welcome Wayne! Thank you for always watching 😊
Thank you for all the videos and i can wait the next video!
You’re most welcome Florin! Thank you for your support. 😊
Hi florin, here is the next video hope you enjoy 😊 th-cam.com/video/RyShRDv92EQ/w-d-xo.html
@@MissMicrosoft thank you 😊
You’re welcome!
isn't there easy way to achieve running total by using List.Sum function?
Thanks....💜
You’re most welcome Mehedi 😊
hahsdhahah good
Thank you!
How great explanation it is !
Glad to hear Alpay 😊