As an accountant I love that you used “closing balance” in your example. ALSO, it would not be a Goodly video without “Please take a look”! That phrase always makes me smile.
The Example „closing Balance“ was fantastic. It gave me so much opportunities for use this „List.accumulate“ Thank you so much. You lift my Powerquery-using-possibilties to another Dimension. Thank you so much. Really excellent and out-Standings Content you offer
Absolutely amazing! Believe me this is the single video available on internet which has explained the List.Accumulate so easily and perfectly that only this lecture is sufficient to watch for complete understanding for this difficult function. Well Done!
this is awesome, i love it, used it already a couple of times, but where i think can be used but haven't been able to is if i want to split into separate columns multiple dynamically, i have many data sets where the desired output would be a "main column" to their "related value", lets say for simplicity you have 4 columns, the "main column" where you have distinct values {lenght, depth, width} but they are not unique values; the next columns would be "mm.1" for the first value in mm, "mm.2" for the second value in mm, "mm.3" for the third value in mm, etc and the same thing for "in" and "ft", the table originally starts as "main column", "mm", "in", "ft". what i do normally is i group by the first column, the "main column" and then manually split the other columns into different columns, getting the desired output. now in my data its normally around the 15 columns ("mm", "in", "ft", "gauss", etc) and to do 15 times that is quite an issue. what this video gave me the idea was to have as seed the grouped table, and from there start to split. up till the present i haven't been able to make it work using the ideas of this video, anything i might be missing? thank you if you read this far and are willing to help btw
Hi Chandeep, after weeks of trying I finally understand List.Accumulate! Thank you so much. The clarity of your List.Accumulate explanations is amazing.
Hi Chandeep - brilliant as usual! Very much like the graduated approach - makes it very easy to understand the complex examples. I can see many potential applications in our current portfolio... Thanks again!
@ 21:05 when you choose which table you want to add the columns to, how come you can't add it to "Source" since that's the table where we want the columns added to?
Not just "absolutely gorgeous" but as well absolutely awesome! It took me quite a well to get a grip on this powerful PQ function (and still not mastered even) but your logical step by step approach makes it so easy. Thanks, Chandeep. I will definitely over my application but this time with an enlightened insight from goodly! 😀
This is incredibly well explained. Thank you very much for. Is it possible to reflect a date (for example, when the training was completed) as a value instead of True or false? 25:58 Best regards Kjeld
Great video, it has helped me a lot, but I wonder, if I have in my dataset many of these columns that I have to map, would I have to make a table duplicating the original? it will not be heavy for powerbi
I used this to find the header row in messy files, looking for the row record that has the largest number of matches with a list of likely/expected column names... Comes up often in common mission-critical files 'compromised' header formats (starting location, multi-line, etc,)
Hey Chandeep, great video and very well-explained as well, helped to understand "(a,b) => function" in power query. Would it be possible to simply use Text.Contains() when decinding for True/False? (Did not go through all the comments, maybe I missed this question). Great job!
Your videos are awesome. I have one problem sir. Please explain the reversal of the last example. For example if we have an OutputTable as our source and I want to concatenate column headers based on true or false values of each record. Complete reverse of this example. Thank you for your response.
Thank you so much for sharing! Your approach through this powerful and complex function was extremely helpful and educational. I really appreciate that you started with the basics and then proceeded into a "real world" example. I learned a lot! 💓
Love this. Great example. It's one of those functions that I find hard to grasp what I can do with it. I now understand it's a lot more then I imagined.
In this last exercise, when you create new columns, you could simply do TextContains(Training, TrainingName), and save yourself splitting, and then couple of iterations
Thank you for the comment. I tried your idea and the code became too small compared to what Chandeep taught in the video. let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Result = List.Accumulate(TL, Source, (s,c) => Table.AddColumn(s,c, each Text.Contains([Training], c))) in Result Moreover, I took another precaution on the TainingList side and used the List.Distinct to remove duplication in the header names.
Fantastic example, and very advanced! If I were to provide "critical feedback" to get you from a Gold Star to a Platinum Star, on par with Alberto Ferrari et all, it would be to spell out some of the "sometimes' hastily given, "end of sentence" explanations. I think you did it once or twice. Otherwise, I love your channel, and I love the ideas that you come up with. Great work!
I always watch this video. In your original table no two people take the same training class. For instance, Rechet takes Power Query and Excel, but no other individual takes these two trainings; so when you create a list of all the training classes from the Training field you have a list of unique training classes. However if for example CJ trained in Trading and Excel then your list would have two instances of Excel. When this happens how do you get your list to have distinct values? In other words, how do you remove duplicates from a list?
Looking forward to watching this, I've used it to create running totals but never really understood quite how it works.. I may have questions.. Enjoyed Promoting two rows to Headers, TableInsertRows could do with it's own show. Richard.
Hi, Excellent introduction to List.Accumulate. I love your videos. How can I handle the case where a TrainingName occurs in more than one row? Add Column will fail. eg. Name Training Rehet Power Query, Excel CJ Trading, Excel
@@GoodlyChandeep Thanks. It worked. Simple solution. What I am not able to wrap my head around is that AddColumn is called for each row then how it works if that column is already there?
This problem hss now been resolved but I don't know why? Hi, I was doing quite well up to list.combine, whch just took me back to where I'd started ie Power query , Fashion Training Fashion , Consulting . etc. Rather than ; Power query, Training, Fashion, etc Have I made an error? List.Combine( List.Transform( List , (_)=> Text.Split( _ , ", " ) ) ) I've read the docs and can see this is not what its supposed to do any thoughts? Richard.
WOW Chandeep, Excelente! lo haces ver tan sencillo! me gustaría ver las otras diferentes soluciones que comentabas al principio del video. Un cordial saludo!
great video. do you have video that shows how I could accomplish the following? I have a list of 4 values. I need to turn that into a list of every possible combination. For example: if the original list is apple, banana, grape, pear. A new list is created and for each row it would contain a unique combination of the 4 original list items separated by commas. so the first row might show banana, apple, grape, pear.................. next row in the list might show grape, apple, banana, pear............ next grape, banana, apple pear..... etc. Thanks so much for the great content.
Hey Chandeep. Nice tut. I have a question though: if I wanted to split each row of a column by two different delimiters that show up in each row several times ( repeatedly). How do I do it using List.Accummulate? For e.g.: delimiter1"text...text"delimiter2 Delimiter1'text...text"delimiter2 Etc
@@chandeepchhabra they are not. 1 row of data is like: $text1# $text2# $text3# And son Then 2 row of data ..... Where $ and # are the delimiters for example
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
As an accountant I love that you used “closing balance” in your example.
ALSO, it would not be a Goodly video without “Please take a look”! That phrase always makes me smile.
*I am pleased that you took a look* 😉
The Example „closing Balance“ was fantastic. It gave me so much opportunities for use this „List.accumulate“ Thank you so much. You lift my Powerquery-using-possibilties to another Dimension. Thank you so much. Really excellent and out-Standings Content you offer
Wonderful!
Wonderful. You opened the way of looping in PQ.
I think the list need to be wrapped in List. Distinct to get unique values for trainings
Yes correct I missed that in the video😬
holy moly, I am blessed to have found you. Severely underrated channel.
Thanks man! Glad to find you also 💚
Absolutely amazing! Believe me this is the single video available on internet which has explained the List.Accumulate so easily and perfectly that only this lecture is sufficient to watch for complete understanding for this difficult function. Well Done!
Wow, thank you!
Que aula top, muita coisa que eu não sabia que dava pra fazer utilizando o List.Accumulate! Parabéns.
Sometimes i hate you cos you make power query look so easy and sometimes i love you because you make power query so much easier. Great job sir.
Genesis! The method used to create multiple columns within a table is brilliant! Thanks for taking the time to share your knowledge 👏
You are an EXCELLENT teacher, Chandeep! 😊 Thank you for making this video and making it freely available.
dear chandeep, your way of explaining the issues is so clear, makes me think that pq is no more a nightmare. thank you…
Thank you! You've been the first one to teach this particular function with clear explanations. You have no idea how helpful your videos are.
You have no idea how much your comment means to me :)
Thanks! Great stuff. Demystifying Power Query. Keep up the amazing work.
Woah.. Thanks Man! 🙏
this is awesome, i love it, used it already a couple of times, but where i think can be used but haven't been able to is if i want to split into separate columns multiple dynamically, i have many data sets where the desired output would be a "main column" to their "related value", lets say for simplicity you have 4 columns, the "main column" where you have distinct values {lenght, depth, width} but they are not unique values; the next columns would be "mm.1" for the first value in mm, "mm.2" for the second value in mm, "mm.3" for the third value in mm, etc and the same thing for "in" and "ft", the table originally starts as "main column", "mm", "in", "ft". what i do normally is i group by the first column, the "main column" and then manually split the other columns into different columns, getting the desired output. now in my data its normally around the 15 columns ("mm", "in", "ft", "gauss", etc) and to do 15 times that is quite an issue. what this video gave me the idea was to have as seed the grouped table, and from there start to split. up till the present i haven't been able to make it work using the ideas of this video, anything i might be missing? thank you if you read this far and are willing to help btw
Hi Chandeep, after weeks of trying I finally understand List.Accumulate! Thank you so much. The clarity of your List.Accumulate explanations is amazing.
Glad it was helpful 💚
This is so advanced, it is beyond most of us. Watching over 5 different sessions, I think I can finally follow through the matrix.
Wow
😁🙏
🤯 I feel so powerful now with List.Accumulate in my toolbox! Thank you, Chandeep! 👏
Awesome! 😁
Have seen many videos on list.accumulate. But none as clearly defined by you. Brilliant 👍🙂
Thanks Sumanth! Glad you liked it :)
Chandeep, You are true rockstar. I feel blessed that I found your channel on youtube.. Keep rocking... 😊
You are a very good teacher. Thank you very much for explaining complex things in a simple way.
Glad you think so!
Hi Chandeep - brilliant as usual! Very much like the graduated approach - makes it very easy to understand the complex examples. I can see many potential applications in our current portfolio... Thanks again!
Glad you liked it!
Chandeep I really thanks for making such indepth video it clear list. Accumulate function concept
this concept was really tough but the way you explained made it very very easy. Thanks a lot
Magnificent!!!
I just don't know what is more magnificent, if the function, or your way of explaining it!
The way you explain is a gift.
Really Glad ! Thanks João!
Thanks for video, Chandeep 🙂
I am impressed by the clarity of the explanation of difficult parts of the issue 👍
Hi Bill. I am honoured to have you here.
thank you!
Nothing else than 😄 really usefull and well explained
One detail a List.Distinct will avoid errors when a training has been done by multiple persons
@ 21:05 when you choose which table you want to add the columns to, how come you can't add it to "Source" since that's the table where we want the columns added to?
Not just "absolutely gorgeous" but as well absolutely awesome! It took me quite a well to get a grip on this powerful PQ function (and still not mastered even) but your logical step by step approach makes it so easy. Thanks, Chandeep. I will definitely over my application but this time with an enlightened insight from goodly! 😀
Glad you enjoyed it Bare!
Brilliant Chandeep, explained this complex technique with ease and such a simple way.
Awesome video.
I’ve watched several tutorials on List.Accumulate. This is the best by far. Thank you, Chandeep.
Glad it helped 💚
This is incredibly well explained. Thank you very much for. Is it possible to reflect a date (for example, when the training was completed) as a value instead of True or false? 25:58 Best regards Kjeld
Great video, it has helped me a lot, but I wonder, if I have in my dataset many of these columns that I have to map, would I have to make a table duplicating the original? it will not be heavy for powerbi
I used this to find the header row in messy files, looking for the row record that has the largest number of matches with a list of likely/expected column names... Comes up often in common mission-critical files 'compromised' header formats (starting location, multi-line, etc,)
Hey Chandeep, great video and very well-explained as well, helped to understand "(a,b) => function" in power query. Would it be possible to simply use Text.Contains() when decinding for True/False? (Did not go through all the comments, maybe I missed this question). Great job!
Brilliant! Nicely done, Chandeep. Very well explained. I'm sure I'll find use for this immediately. Thank you very much.
Sure! Glad you like it
This is genius. Couldn't find anything like this in any textbook.
Your videos are awesome. I have one problem sir. Please explain the reversal of the last example. For example if we have an OutputTable as our source and I want to concatenate column headers based on true or false values of each record. Complete reverse of this example. Thank you for your response.
Really a great tutorial ! Thank you so much ! I’ m sure I will use in my job.
Sure man!
Clear and thorough teaching. You're an excellent tutor. Thank YOU.
You're very welcome!
Your way of explaining this list.accumulate is simply awesome. This is very powerful function!!
Thanks for the efforts you had put in here.
Glad you like this A!
Thank you so much for sharing! Your approach through this powerful and complex function was extremely helpful and educational. I really appreciate that you started with the basics and then proceeded into a "real world" example. I learned a lot! 💓
Glad it was helpful !
Great Goodly!!, Made simple to under List.Accumulate,
Thanks!
Your explanation is wonderful one can easily understand even Complex formula
Thanks 💚
You are a great trainer!! Thank you for this easy to follow tutorial!❤❤❤❤❤
Love this. Great example. It's one of those functions that I find hard to grasp what I can do with it. I now understand it's a lot more then I imagined.
Cool!
In this last exercise, when you create new columns, you could simply do TextContains(Training, TrainingName), and save yourself splitting, and then couple of iterations
Thank you for the comment. I tried your idea and the code became too small compared to what Chandeep taught in the video.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Result = List.Accumulate(TL, Source, (s,c) => Table.AddColumn(s,c, each Text.Contains([Training], c)))
in
Result
Moreover, I took another precaution on the TainingList side and used the List.Distinct to remove duplication in the header names.
Fantastic example, and very advanced! If I were to provide "critical feedback" to get you from a Gold Star to a Platinum Star, on par with Alberto Ferrari et all, it would be to spell out some of the "sometimes' hastily given, "end of sentence" explanations. I think you did it once or twice. Otherwise, I love your channel, and I love the ideas that you come up with. Great work!
I always watch this video. In your original table no two people take the same training class. For instance, Rechet takes Power Query and Excel, but no other individual takes these two trainings; so when you create a list of all the training classes from the Training field you have a list of unique training classes. However if for example CJ trained in Trading and Excel then your list would have two instances of Excel. When this happens how do you get your list to have distinct values? In other words, how do you remove duplicates from a list?
13:18 Could you please let me know what the last video was - where you gave that problem?
Thanks.
goodly.co.in/create-dynamic-columns-power-query/
Looking forward to watching this, I've used it to create running totals but never really understood quite how it works.. I may have questions.. Enjoyed Promoting two rows to Headers, TableInsertRows could do with it's own show.
Richard.
I watched this video again. Chandeep is that dude. My goodness he has a talent for providing instruction.
This is insane! 🙌🏻🙌🏻 You are a god ! Thank you so much.
Thanks your Tutorial Power Query
Special thanks for sample file
I Can step by step with sample file
thanks very much
Thank you so much Goodly for this masterpiece 😊
Thanks Chandeep :)
I just added a List.Sort on AllTrainings list to order the courses alphabetically, easier to read :)
Totally enjoyable, thank you Chandeep. Also using the Closing Balance analogy really helped with the explanation.😊
Thanks Susan. Glad you enjoyed it :)
Great video!
One question, how would you handle this is 2 people took the same training class - let's say someone else took the Excel class too?
I'd just use list.distinct while creating a list. Rest remains the same
You're a really really great tutor!!!
Thank you!
Q- in ur example of mom and dad had same training events would this still work? Or do I need to do a list mandated with unique items ?
I learnt a lot from you. Thank you Chandeep!
This is amazing. Thank you!❤
Hi,
Excellent introduction to List.Accumulate. I love your videos.
How can I handle the case where a TrainingName occurs in more than one row? Add Column will fail.
eg.
Name Training
Rehet Power Query, Excel
CJ Trading, Excel
While creating a list for training names, surround that with List.Distinct
@@GoodlyChandeep Thanks. It worked.
Simple solution.
What I am not able to wrap my head around is that AddColumn is called for each row then how it works if that column is already there?
Absolutely is brilliant 👏. Best way to show the matrix .
Thank You !
the quality of these videos are mind blowing.
Glad you think so!
You sir are a phenomenal instructor.
Thanks Michael!
Thank you for lesson! Marvelous!
Amazing. Brilliant stuff! Will take me a few watches to learn for sure!
Thanks Kim!
Love it love it love it... great job..
Thanks 💚
Nice Content really, Can we calculate Running Total with List.Accumulate function?
Awesome examples. You are now a go to person for me to help solve problems!
Glad to help!
I’ve had university lecturers with teaching qualifications and PhDs in their field that don’t teach as well as Chandeep.
Oh my god.. thank you so much 💗
"I'm gonna say :hey!..you are the best
What an amazing video. Hats off! Thanks a lot!
Glad you like it!
Thank you for the great explanations.
Great vid! Hey is there a similar function for lists going horizontal (so for a (part of) rows)?
Thank you! Brilliant and useful as usual!
AWESOME! Thanks!
Closing Balance is a great sample!
Glad you like it!
You really really really knock it out chandeep.
Thanks! 💚
Great teacher...takes time to break things down.
💚
I’m going to have to watch this a few times!! Nice work Chandeep!!
Thank You Martin!
great sir learned a lot .I working on weighted average cost .I think this will solve my problem.
Awesome thanks
You are Amazing
So helpful ^^, thank you 🙏.
so great tutorial!! Thank you so much!
this is like the reduce() function on JavaScript. Interesting how this also uses arrow functions like the latest JS
This problem hss now been resolved but I don't know why?
Hi, I was doing quite well up to list.combine, whch just took me back to where I'd started ie
Power query , Fashion
Training
Fashion , Consulting . etc.
Rather than ;
Power query,
Training,
Fashion, etc
Have I made an error?
List.Combine( List.Transform( List , (_)=> Text.Split( _ , ", " ) ) )
I've read the docs and can see this is not what its supposed to do any thoughts?
Richard.
Amazing function and fantastic explanation
Glad you liked it!
Oh man nice one again. 👍
Thanks Man!
thank you Chandeep i m grateful
You're most welcome
WOW Chandeep, Excelente! lo haces ver tan sencillo! me gustaría ver las otras diferentes soluciones que comentabas al principio del video. Un cordial saludo!
Gracias!
Thanks.i thought i need watch your video more :)
Thanks a Lot sir
I will try to used in Financial Area I need it
great video. do you have video that shows how I could accomplish the following? I have a list of 4 values. I need to turn that into a list of every possible combination. For example: if the original list is apple, banana, grape, pear. A new list is created and for each row it would contain a unique combination of the 4 original list items separated by commas. so the first row might show banana, apple, grape, pear.................. next row in the list might show grape, apple, banana, pear............ next grape, banana, apple pear..... etc. Thanks so much for the great content.
In the third argument for state and current, is there any other functions we can use as accumulator?
Just WOW!!!! :D Thanks a lot!!!
I combine the 2 text, but i want 2nd text should start from second line , is it possible
Hey Chandeep. Nice tut.
I have a question though: if I wanted to split each row of a column by two different delimiters that show up in each row several times ( repeatedly). How do I do it using List.Accummulate?
For e.g.: delimiter1"text...text"delimiter2
Delimiter1'text...text"delimiter2
Etc
If the delimiters are same use text.split
@@chandeepchhabra they are not.
1 row of data is like:
$text1#
$text2#
$text3#
And son
Then
2 row of data
.....
Where $ and # are the delimiters for example
@@erivaldolopes632 use text.betweendelimiters
Absolutely amazing !!!!!
Excellent 💯👍👏
Thank you !
Aweshome!!! Amazing!!