List.Accumulate in Power Query with Practical Examples

in today's video i have the perfect opportunity to talk about the function called list dot accumulate i'm sure you must have heard of this function list or cumulative power query really really powerful and i would like to demonstrate how this function works to you and use the problem that i gave you in power query to talk about its capabilities and how can you use this function now in order for me to structure this entire video i'll straight away jump into list.accumulate talk about simple examples talk about complicated examples i will also talk about the solution to our problem using list or accumulate and in case you believe that it's too complicated we'll also talk about a simple solution to solve the same problem all right no further ado let's get started all right before i start to delve deep into examples of list list.accumulator it's very very important to understand the syntax of list.accumulate what do you write inside of that function what does the function do what do you get as an output of the function and once we have covered a bit of theory we'll actually take a look at a few good practical examples please take a look the list.accumulate function as the first part asks you for a list and list.accumulate function is an iterator function it can go loop through every single item of the list and can accumulate the result over the next item what do i mean by that i'll just help you understand so let's just say that we declare a particular list and if you don't know what a list is list is nothing but a single columnar data structure in power query that can hold just one column but perhaps multiple rows so let's just say that we have a list and in the list we have a couple of items uh that we have to iterate through now the list.accumulate function what it's going to do is it's actually going to take the first item and do something with that particular item it could be anything now once that something has been done it is going to take the result of the first step and accumulate that over the iteration of the second step now once it's done with the second step then it is going to take the result of the first two steps and accumulate that over the third step and so on and so forth that's how the list dot accumulate function works not only there is an iteration but there is also an accumulation of all the previous step you know putting together into the current step in the iteration all right the next input that you happen to write in the list list.accumulate is the seed or the starting point now i'm happening to use a lot of english terminologies just to help you understand right so what is a seed seed simply means that when you're trying to do your accumulation what is your starting point do you want to start with something blank do you want to start with 0 do you want to start with a certain table whatever you would want to start with that is nothing but your starting point now note that in the list.accumulate function the starting point could be anything it could be a number it could be a text it could be a list it could be a table it could literally be anything all right now when you start to write list dot accumulate you happen to declare two variables one is the state variable the other one is a current variable what do i mean by these state and current variables please take a look let's just say that i happen to define a list and the list has got three items right every single item of this particular list is going to be as a current so when the list goes through an iteration now there are obviously going to be three iterations iteration number one iteration number two and iteration number three in the first iteration the first item is going to be denoted by current when the iteration happens for the second time then this the second item is going to take the place of the current and finally this becomes current at the third time so current is nothing but the current item of the list that you have declared all right now what exactly is the state state is nothing but the output of every single iteration now if you have taken maybe classes in accounting and finance it's very very easy to understand what is the closing balance so imagine that something like i am doing an iteration over three items and the iteration produces a result right this is my result this result which is the output of the first iteration is nothing but the state now this is nothing but the closing balance right the closing balance happens to be the opening balance of the next step and then something again happens then you again get the result this is again the state which is the output of the second step which is nothing like a closing balance and the closing balance happens to be the opening balance of the next maybe iteration then this actually becomes the state and finally when all the iterations are complete the state happens to be the output which is the output of the final iteration the accumulated result happens to be the output the beauty of list.accumulate function is that the output can be a table it can be a list it can be a single number and that is what makes the function absolutely magical to work with now enough talking we have done a bit of theory i'm not really sure if you've understood anything i hopefully will be able to help you understand with a lot more practical examples so let's just jump over to example and let's have fun with this function all right in power query here and let's just start to work with a ridiculously simple example to put the concepts into play talking about list.accumulate right from scratch the first thing that i'm going to do is i'm going to declare a list and the list is going to have three letters a b and c let's just start with that so in the source step i'm just going to start writing equals to a list and list obviously is going to be defined in the curly brackets i'm just going to say that the first item is a the second item is b and the third item is c now once i commit to this i'm gonna get a list and that is the list that i would want to iterate through what is it that i'm trying to achieve as an output through list accumulate is nothing but the concatenation of the three letters that i have that means as an output i would like to get a concatenated with b concatenated with c this is what i would want to see as an output now sure enough there are several ways to do it but i'm going to try to explain list or accumulate through the concatenation example all right now the next thing that i'm going to do is insert a new step and in this particular step i'm going to start writing list start accumulate so i'm just going to say list dot accumulate as the first part of the list.accumulate function if you remember the first part is nothing but the list that means which list do you want to iterate through so take a look it's also asking you that hey do you have a list that you would want to iterate through sure enough we do have that list and that was the list that we declared on the first step which was containing the three letters and that has been defined as nothing but my source so i'm just going to feed my source step right here and i'm good to go with that first part the next input in the list.accumulate function is the seed or your starting point that means how would you like to start now for just a second let's just try to imagine the output that we're trying to achieve we are trying to achieve a concatenated with b concatenated with c but if i want to have this particular output and concatenate all the three items together what do i want to start with that means do i want something particular at the start here at the concatenation the answer is no so i'm saying that hey why don't you start with the blank string nothing just like two quotation marks and that's what you start with right now i'm going to say that hey then it says that what's your accumulator function how would you want to accumulate items in that list now if you remember that we had two variables that we would want to declare so if i open the bracket right here i'm going to say that the first variable is state and the second variable is a current now note that state and current variables could also be named something else so you could just call it hello and hello2 and this is also going to work just fine note that the position of the variable decides what is going to be the input of the variable whatever you write here as a variable whatever name is going to be the output of the step whatever you write here is going to be every single item of this particular list so the word state and current is just a common practice that everybody follows should you want to follow this practice you can follow it in case you don't want to follow it that's totally okay so i'm going to declare two variables one is the state the other one is the current and i'm going to convert this into a function now it's time for me to write a certain function that concatenates every single item and then combines it so how do we do such a thing imagine that we have this particular list i mean you don't have to imagine we really have that list so we have a b and c and i'd like to concatenate every single item now as a fact you already understand that list.accumulate is nothing but an iterator function that means it is going to go through the first row then it is going to go through the second row then it is going to go through the third row that is the property of list.accumulate right so what do i want to do in the first row i start with nothing so nothing is my like a blank string and then i say that hey why don't you pick up a and concatenate that so i'm just going to say that my starting point is nothing and i'm just concatenating that with the current because current happens to be the first um value that the current is going to store because in the iteration a happens to be the first input so a is going to be denoted right here now what is going to be the output here so nothing combined with the current current is nothing but a so you're going to get a as an output now once a becomes the output of the first step that particular a value is now going to be stored in the state variable right here now once that particular value has been stored in the state variable then that becomes your starting point so i'm just saying hey why don't you take your a and concatenate again that with current so c u r r is nothing but current and then now the output is going to be a concatenated with b and this is going to be your uh input or output of the second step and input of the third step and then so on and so forth so i just have to write something like hey take the state and concatenate that with the current and that's about it now if we commit to this i'm going to get concatenated a b and c and i hope that you understood that how the concatenation was happening through the three stages of accumulation and iteration in that list all right let's just start with example number two still very simple and then we'll graduate to a more complicated example all right fellas example number two we have already created a simple list which contains five numbers one two three four and five and what i'd like to do is i'd like to square every single number and then sum the result what do i want so 1 square is 1 2 square is 4 3 square is 9 4 square is 16 and 5 square is 25 once i get the squares of every single number i then want to sum it up and the answer is going to be 55. that's what i would want to see as a result the number 55 is what i'd like to see how do we do such a thing through list.accumulate now let's just start so i'm just going to maybe create a new step and start to write lists.cumulate so i'm just going to say hey list dot accumulate and um i think i've made a spelling error all right let's start accumulate the first part it's asking you hey do you have a list that you would like to accumulate through sure enough i have created the list and the name of that list is nothing but the first step which is nothing but my source i'm just going to feed the source tag right in here then it says hey do you want to maybe start as a seed that means what's your starting point now note that this at this particular stage i am trying to perform arithmetic on numbers and my starting point cannot be like a text as we had it before because we were working with text values now now once you're trying to start and we have the five numbers one two three four and five my starting point could be zero because if i add something to zero it's going to be just fine so my starting point is just zero so i can just write that zero happens to be my starting point then it asks me hey what's your accumulator and this is where we have to declare the two variables the state and the current now just for a change i'm now going to call these variables as state and current i'm going to call something else and you will see they actually mean the same thing even if you change the name statement current to something else so just for fun i'm going to call this as s and the other one is that i'm going to call it as c just to help you understand that s is state and c is current all right now let's just call the function so i'm just going to maybe write the accumulator function now what i have to now close down on is decide that how is the function going to happen and how is the accumulation going to happen at every single place now let's just go back to the basics and start to work out our logic if you take a look at this particular list right here in the first step we have five numbers now once the iteration starts the first item is going to be one that means one happens to be my current right so what is what is going to happen is that i would want to take the current and multiply it with current once again so current into current is going to give me the square this is again going to give me the square this is again going to give me this here so current into current so if i write current into current it's actually going to give me the square but the problem is that i'm not really doing the state anyway that means once the multiplication has been done this particular result needs to be added to the second iteration and this particular result needs to be added to the third iteration so what do i do now i know that the result of every single step is stored in the variable called state i can write something like this hey take this current into current and plus what plus the state which is nothing but the output of every single step easy enough so i'm just going to come right here and as a function you always write how the values are going to get accumulated so i'm just going to say hey first of all i would want you to take the state not state but s this time because we declared s so s plus and i'm just going to say c multiply by c and that is nothing but my square hopefully it should work i just close the bracket and press enter and that gives me the 55 answer that i was looking for awesome all right let's just start to work with a real example to solve problems using list dot accumulate now this is the problem that i gave you in the last video there are many ways to solve this problem but i will take the opportunity of explaining you how does list dot accumulate function work on this particular type of the problem please take a look just a quick recap of the problem we have names of people and we have different training programs that these people have done and i would like to create a column for every single training program that means that my son rahat here has done two training programs one is power query the other one is excel so i'm just going to build two columns um right here one is going to be power query the other one is going to be excel and i will mark true and true and there are going to be other columns as well so there's going to be a trading column a fashion column so all of the training programs here are going to have a column and whosoever has done whatever training is going to be marked as true so for example cj has done trading it's going to be marked as true against in that column so how do we do such a thing using list dot accumulate let's just do some bit of manual work try to understand the code that gets generated and take a look at does it match the behavior of list.accumulate or not so real quick i'm going to add a manual column so i'm just going to go to the add columns tab custom column i'm just going to say hey why don't we create a manual column called power query and in that column just for you know making things work we'll just write one for now let's just be done with that i'm just going to say uh power query and this is the column that is created i'm going to go back to add columns tab and create one more column i'm just going to say this is going to be excel and again one right here say okay and that's the column that has been created now if you closely take a look at the syntax it clearly matches the pattern of list.accumulate please take a look if i go back to the first column that we added in the table dot add column function that was generated there were three critical parts the first part was hey in which table would you like to add a column so i'm saying hey the source step which contained only two columns please add a new column what is the name of the column that's the second part is the name of the column is power query and what's the third part it's some formula that is going to generate true and false right now this is one pretty good now take a look at the next one on the table which was already generated we accumulate the next table that means on top of this table we're going to take that table as the input or the state and we're going to accumulate one more column so take a look in this particular step the table that we're trying to use is nothing but the previous step table that means we are trying to accumulate one more column on the previous step the name of the column is excel and the value of that column is one in this case now it clearly qualifies for the behavior of list.accumulate because we are trying to accumulate the table of the previous step and add a new column on top of that add a new column on top of that that is pretty well so what do we do is we first have to take all of these training programs which are going to be added as a column in a form of a list and once we're able to extract all these training programs in a form of a list then we'll be able to work with list.accumulate in a good way so that's the first part let's just get done with that all right the first thing that i'm going to do is i'm going to duplicate this particular query to be able to convert all of these training programs into a list right click on the data query i'm just going to say duplicate and i get one more query right here i'm going to call this as training list so training list good to go now from this particular table i don't really need the name column here i just need the training column in here so i'm just going to create a new step and i'm going to say that hey please extract the training column from this particular table table name column name in the square bracket is going to give you what a list if i now commit to this i have got the list however the problem is that power query comma excel is not really the kind of column that i'm looking forward i'm looking for a power query column and an excel column so i need to break these training programs which are separated by commas into different rows how do i do that so what i'm gonna do is i'm gonna maybe go ahead and i'm gonna say that i would like to split every single item of this particular list by a comma as a delimiter so how do i do that i'm going to say hey i have a function called list dot transform so list dot transform and um form f o r m and i'm going to say hey here is my list this is nothing but my list and i want to pick every single item that means each item i would want to pick and i want to delimit that with a comma so i'm just going to say hey pick up each item and apply which function text dot split function on every single item and the delimiter happens to be a comma this is good press uh enter and let's just see what we get now once the items were split and you got two items those two items were returned in the form of another list that means although you have two different rows for two different training programs but they are kind of broken into two different like in a list format now all that i do is i just combine the values of all of these list items that i have and i'm gonna get a single list so i'm just gonna say that i would like to combine all of these lists that i have received and that should actually do well press enter and all of the lists smaller lists they have been combined to give me one single list now what i'm going to do is i'm going to use this particular query which contains the list and create a column for power query create a column for excel create a column for trading so on and so forth let's just see how can we do that using list dot accumulate all our people the fun begins here lists not accumulate so we have made this training list and we have to now create columns for every single trading program that is mentioned in this particular list how do we do that using list dot accumulate i'm going to create a new step in here and start to call my function which is nothing but list dot now as a first part of this particular function list.accumulate it's asking you that hey do you have a list in which you would like to do the iteration that particular list is nothing but the names of these training programs that we have collected right here so i'm just going to feed that right here that means my list is training list is my list all right then it says hey what's your seed or what's your starting point now in our earlier cases we have mentioned the starting point as 0 we have mentioned the starting point as a number or like a text or something or the other here my starting point is nothing but a table now note that the thing that we are trying to do is nothing but adding a column and adding a column could only be possible on a table now on which table would we want to start adding a column so this particular table which is nothing but the source step contains the two columns and here is where i would like to start adding a column so this entire source table becomes the seed that means when you start doing your iteration start by adding a column to this particular source table so i'm just going to say that my seed is nothing but source okay then it says hey what's your accumulator so i'm going to say that just like the way that we have to declare two variables one is the state the other one is the current this time i'm going to name them differently all right so i'm just going to say that my two variables are so this is my output table now remember this because i'm writing this as the first this this is going to be my state that means all the outputs of every single iteration are going to be stored in this output table right and this is going to give you a table as an output and the second accumulator or the func the second input is going to be each training or let's say training name so training name and that is going to be my current right and then it asks you hey what's your function that means once i go through every single training name what do you want to do so what do i want to do i want to add a column so i'm just going to say that i would like to add a column so table dot add column happens to be my function that i'm going to use just as the way that we saw it in the example once we were doing some manual work table dot add column function is going to ask for three inputs first input hey to which table are you trying to add a column right so i'm trying to add a column to whatever is the output so output happens to be my table so output table is the table on which the new column is going to be added then it says hey what should i name the column once a column has been added what should i call that so the name of the column is going to be nothing but the name of the item in the training list and where does every single item get stored in the current variable right now we are calling that current variable as nothing but the training name so i'm just going to say that hey once you create a column call that column as nothing but the training name all right then it says what should i put inside of that column that means in that column that we're trying to create what value do you want to see so as of now i just want to see one everywhere so i'm just trying to make it work now as soon as i kind of commit to this particular function what do i get i get a column added to the table right here so initially this was my starting table then the first column was added this became the input to the second step then another column was added and that became the input of the third step and then another column was added so on and so forth till the time it went through all the columns all the values of this particular list and added all of these columns to this particular table all in just one step now the problem is that as of now this is just giving you one as an input it's not really giving you the true and false that we would ideally want now the next job is going to be to ideally convert the one into a true or a false that's the ideal output that we're expecting all right the problem that we're trying to solve in order to get true and false is i'm trying to check that why don't you consider both of these items separately that means power query is one training and excel is another training consider them separate different words and why don't you compare that with the header or not if power query equals to the header then you write true here if excel equals to the header then you write true here otherwise you can just write keep writing false everywhere else that's what i'm trying to do now in order for me to be able to get that i first need to break these items as two different items and not one single text string now as of now what i have done is here i'm writing one but instead of writing one here what i'm trying to do is i'm trying to capture these two items as two separate pieces of texts let's just do that i'm going to say that i don't really want to write one and i want to split the text i want to split this particular text by a delimiter which is nothing but a comma and a space so i'm just going to say hey i'm using a function dot text dot split split and i'm going to say hey the text is going to be kept in the column name called training and the delimiter happens to be a comma and a space now let's just see what happens if i just maybe close the bracket right here and kind of commit to this function i get a list in that particular list i have broken the two trainings that i had which is nothing but power query and excel they are now two separate words now the next obvious thing that i should be doing is that i should be taking this particular word and comparing that are you there in the header of the column or not if you're there that means you did attend the training and i'd like to give you a true then here when once i go in this particular list which is again nothing but power query and excel i'm going to say hey are you there in the header or not if you're there in the header then i'm just going to maybe give you not a false but actually a true how do we do such a thing i'm going to say that in this list if any of the items if any of these items of the list does it match the header or not how do i write such a thing i'm going to write a function called list dot matches any all right now the list dot matches any function is asking you hey do you have a list in which you'd like to run some match the following kind of thing yes we have this particular list which is where we would like to match what am i trying to match in this particular list i'm going to say that hey every single item of this list which is power query excel and all the training programs each item of this particular list is it matching with the header now the problem is how do i capture the header of the column now remember that how do we place the header in the first place the header was nothing but every single training program that was mentioned in this particular query which is right here this particular query so that particular name of the program came from the current variable in our case the current variable is nothing but the training name so i'm just going to say that hey every single item of the list is that equal to the training name or not so i'm just going to maybe write training name right here and i think that should be good uh i just have to close one more bracket and commit to this press enter and voila take a look we have power query and excel power query matches here it gives me a true excel matches here that gives me a true and rest everything else happens to be a false this is absolutely gorgeous all right that was all about list dot accumulate i hope i was able to help you understand and make you navigate through list or accumulate through various complexities of the problems that we discussed right here although this particular problem need not be solved through list or accumulate there are several simpler ways of solving it you should take a look at the blog comments and the answers that people have replied and you should actually learn from those solutions as well i was trying to particularly use this example as a good candidate to describe the nuances of list.accumulate and probably describe a few practical examples through this one i hope you enjoyed this one and in case you did participate in the challenge a big big big shout out to you thank you so much for taking out the time and participating in this challenge all right in the end i'd like to give a big shout about my tax and my power query courses in case you're starting out with power bi and you'd like to learn the fundamentals first and then proceed on to solving more challenging more difficult problems of your own data be tax data modeling or power query i highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking around indeed this was a very long one if you have any questions put them down in the comments and i will be glad to reply thanks so much and i will catch you guys once again in the next one [Music] you

Channel: Goodly

Views: 46,497

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: RIo4OrPixco

Length: 27min 26sec (1646 seconds)

Published: Tue Jul 26 2022