each & underscore_ in Power Query Explained

this has been the most requested video so far for each and every one of you I'm gonna talk about the each keyword in power query words would not do any justice let's just go deep dive and take a look at what the hell is each in power query let's go when you're trying to understand the each and the underscore the each is the easy one because it means the same like every single item in a collection but what does the underscore mean the underscore on the other hand changes the meaning depending upon the context in which you're using it let's just take a look at a practical example now I have a table three columns right here and I would like to add a column to this table for that I'm going to use a function called table dot add columns let's just start with that so table dot add column and I'm gonna maybe start to write the name of the column so let's just say new the first part of the formula is source source happens to be the name of the entire table which is nothing but the previous step new happens to be the name of the column that I'm creating and after that I'm going to say that I would like to write the each which means every single item but what does the item mean right here I'm going to write the underscore right here and close the bracket now as soon as I happen to use the underscore in the context of the table it simply means every single item of the table and the item here is nothing but the row so this is the first record the second record record means row and the third record and so on and so forth now as soon as I commit to this particular function it's actually going to create a new column in here which is where it'll actually capture the record so if you peek into this record right here you're going to see that the entire data of the first row has been captured right here now since I happen to use the underscore in the context of the table the underscore meant that you're trying to catch hold of the record of this particular table now once we have been able to extract the record which is right here I could do anything with that record which is now stored in this little variable right here which is nothing but the underscore let's just say that I'm trying to maybe capture like a column of this particular record right here so the record has got three columns date sales rep and amount maybe I'm just trying to capture the amount right here so how do I do that there can be another function which is nothing but let's say record dot field and I'm going to say that and the underscore gives me the record and I'm going to say that my field is nothing but the amount and I'm going to close the bracket and press enter now what this is going to do is it's going to go inside of that record which is stored in the variable underscore and just pull up the amount and give you the answer which is nothing but the amount right here now like I said that the underscore changes the meaning depending upon the context let's just take a look at the each and the underscore in the context of a list okay I'm playing with a simple list right here which has got just five numbers and I will try to write the each and the underscore in the context of the list now let's just create a new step in here and I will use a list function this time since I'm working with a list so there happens to be a function called list dot transform the first part of the list.transform function is to provide a list and Source towards the step just previous which contained the five numbers and that's my list and now let's just happen to write the each keyword so let's just say each underscore and press enter now the underscore here means every single item of the list which is not a table so it doesn't mean a record anymore since the context has changed this time the underscore is going to capture every single item of this list so the first time it's going to capture one the second time is going to capture two the third time is going to capture three so on and so forth and as soon as I press enter nothing changes it's going to give you the same value because you just captured that value and put it right here you did nothing with that let's just say that for every single item of the list I would want to add a 1. so I could probably say something like now that the underscore has captured one maybe I'd like to add one to that so I'm going to say something like plus one and as soon as I do that you now have counting from two to six so the point that I was trying to make which is very very important is that the each keyword is not very hard to understand it means every single item but the underscore part which refers to the item is going to change the meaning depending upon the context in which it has been used if you're using it in in the context of a table it means a record if you're using this in the context of a list it means every single item of the list an item of the list is every single value I hope you understood that we're not done yet the each and the underscore happens to just be a sugar syntax or maybe an easy way of writing functions that make the syntax of power query a bit easy to read now let's just take a look at the same query that we had been working with in the past which is where we had the table and we use the table dot add columns function to add a column and each underscore is the keyword that we wrote Let's just delete that for just a bit and to start to read that what the formula is asking for so if I just put in comma here you can see that the table dot add columns function is asking me to give me a column generator as a function that give me a function that could generate a column it's not asking you for the each and underscore here it is asking you for a function and as a simple syntax of creating a function we happened to write the each and underscore now here let's just start to add a function let's just see that is that similar to writing in each and underscore or not so I'm gonna maybe go in here and first start to declare a variable and let's just open the bracket and declare any variable let's just call the variable R you can call it anything but for the moment I'm going to call this as r as soon as you declare the variable r r is going to hold up every single item of the table and like I said every single item of the table is nothing but the record of the table or the row of the table so R here means the row of the table now how do I convert the R into a function so I'm just going to maybe start to write an equals to sign and a greater than symbol this rocket sign right here means function anything that you write after that happens to be a function and that's what the formula is asking me for so I'm going to say that hey I don't really want to do anything just give me the r back which is nothing but my record back I'm going to close in the bracket and press enter and what do I get I get the same output like I was able to get with the each and the underscore and the each and the underscore happens to be an easy way to read power query syntax but this happens to be the same and we can do something with that r r happens to hold the record value for every single row and we could do something with it so let's just say that we would want to again and extract the amount from here let's just extract it in a slightly different way so this time I'm going to say that hey R happens to hold the record the record has got three columns date sales rep and amount and I would want to extract the amount so I'm going to say that hey square bracket and I'm going to say amount and this time as soon as I press enter I'm going to get the amount of that particular value right here awesome now let's just start to create functions in a list and let's just see if it also means the same over there all right I'm back at my list which is where just a while ago we had five numbers one through five and to every single number we added one using the each and the underscore keyword let's just happen to delete the each and the underscore keyword and let's just start to create a function let's just quickly verify that as well so if I just happen to write a comma again you can see that this is asking you that hey please give me the transformation that you would like to apply as a function so let's just start to create a function this time I'm going to maybe declare the function variable as X and I'm gonna maybe start to write the function keyword so which is nothing but the equals to and the greater than sign this converts this into a function what has X done X has captured the value of every single item of the list whatever the underscore was doing it earlier the x is doing it now the only difference is that X is now used in a function so I'm going to say x happens to hold every single value I'm just going to maybe want to have the X again if I do nothing with it with X I'm just going to get the values back one two three four and five but if I happen to let's say add plus one right here I'm gonna get let's say a two three four five and six which were just one incremented from one now what you could do is that using a function you could also happen to declare the underscore as a variable so the variable like I said could be anything it could be x y z anything but whatever you happen to write here is going to hold up the value of every single item of that particular structure that you're working with table record list just one single item so this time I'm gonna say that hey X underscore plus one which is not going to give you the same output right here so just another way of writing it each and underscore or you could write a function and you now know both of them all right that's been eight the each and the underscore in power query let me know how did you find this one in case you have any questions around this please feel free to drop in a comment and I will be glad to reply obviously I try to use some very simple examples to demonstrate how the each and the underscore works but if you happen to use a very complicated query and you're maneuvering between the table and a list and other items of power query structures then each an underscore would mean different items of that particular object structure that you're trying to work with I hope you understood that part really really well let me know if you have any questions around this and I'll be glad to reply in the end a big shout about my Dax and my power query courses in case you're starting out with power bi please feel free to check out my courses they're going to take you from a beginner level to a more advanced level and help you solve more sophisticated and more advanced problem even of your own data thanks so much for watching this and I'll catch you guys in the next one cheers bye foreign [Music]

Channel: Goodly

Views: 40,622

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

Id: IHgHvHaWfpM

Length: 9min 57sec (597 seconds)

Published: Wed Oct 19 2022