Getting Started With M Language in Power Query | Basic to Advanced

i don't know about you but the first time when i took a look at the m code in the advanced editor of power query i really really got scared and i said it aloud what are these worms floating around in this video i'm going to talk about the construct and the fundamentals of understanding the m language in power query so that once you take a look at the advanced editor it looks a lot less like worms and you understand what's going on let's start all right this might be a little longer than the usual videos that we do and in order for me to make this video a lot more structured and easy for you to understand i'm gonna divide this entire video into three main buckets or three parts part number one we're gonna take a look at the syntactical requirements that you have to adhere to when you're writing m code what happens in the advanced editor part number two we'll actually take a look at a query that i write in the advanced editor and how does the syntactical requirements play a very important role so i'll maybe write an example query and part number three is going to be different objects which are there in the m language and how could you probably use them objects like stables lists records things like that no further ado let's start all right i'm in excel power query right here i've done a few transformations on the sample data that i have and i'm already assuming that you have the preliminary understanding of how the query works how do you apply a bunch of transformations this is not however our playing ground we're going to take a look at the advanced editor and take a look at a few syntactical requirements let's just start with the first one if i just open up the advanced editor in the view tab i'm gonna see that this particular query right here has got three steps and those three steps are also named right here source filtered rows and remove columns now the first understanding that you need to build is that every single step is a variable of its own right so take a look at this particular source step in the source step we are actually fetching the data from the current excel file and a bunch of things around that we're not really concerned as to what exactly are we doing right here but we're just concerned with this particular step source step now once the table from excel has been loaded this particular step contains the table and if you take a look at this particular step this step actually goes as a feeder function to the next step which is nothing but filtered row so whatever comes from the source there is a filter function applied in power query through table dots electrodes and this becomes a feeder and whatever is the output of this particular step which is filtered rows this becomes a feeder to the next step right here so every single step that you create could possibly be holding any value it could hold a number it could hold table it could hold any function it could hold any value and that is nothing but a variable that you can actually also allocate to other steps and you can call these steps the names of the steps that you write should not ideally contain spaces but if you decide that you would want to write a space between the name of the step then you'll have to put quotation marks and prefix that with a hash symbol take a look source did not really have any space in the name because it was just a single word so it's written in the usual way but filtered rows has got a space in between therefore we had to provide a quotation mark and also prefix the the hash symbol at the start of this particular step name all right point number two really really important this is also a place where you will tend to make mistakes if you decide to write the m queries right from scratch in the advanced editor please take a look so if you take a look at the advanced editor every single step is going to have a comma in the end to close that particular step but not the last one so take a look the first step right here has a comma in the end the second step right here also has a comma in the end but the third step which is the last step of the query because there is no further step after that does not have the comma in the end and therefore it is the last step so make sure that when you're writing the query and if you're re-juggling the query the last step doesn't contain the comma but every single step does contain the comma point number three the let and the in keyword are the two key phrases that let you define the steps in between in case you have worked with dax in dax we have the var keyword and the return keyword which lets you define different steps or different variables in between just like that in power query the two keywords are let to begin declaring the variables or the steps and you will close that with the in statement now take a look because we wrote the let function right here we are able to declare three steps which is source filtered rows and remove columns and as soon as we write the in keyword that means we are trying to close the loop of declaring any more steps any further and then after that we can just go here and call the step that we would want as an output so take a look we did three different variables right here and our last uh step was remove columns this is where we closed the the variable declaration or the steps you know any further and we are calling this particular step as an output right here after the in statement so this works very very similar to the var statement and return statement index but here in power query we have the let and the in keywords which are fundamental to define variables or steps in between point number four the steps that you create in power query may or may not follow a logical or coherent order what do i mean by that please take a look right now we have a source step the source actually feeds to the next step and the next step is filtered rows which actually feeds to the next step right here and the remove columns which is actually the output so now if you take a look at this particular query our query is actually flowing into a certain order the first step goes to the next the next goes to the next and then we retrieve the output but that's not a mandate for the query to work i can also juggle these steps and put them in a jumbled order and the query will actually still work so if you just go ahead and maybe put a comma because that's a requirement towards the end we need to have a comma in the second step we'll also need to have a comma but in the third step we may not have a comma i'm just going to get rid of the comma and you can see that no syntax errors have been detected and my query is still going to work if i click on done i still get the very same output that i was getting it earlier my query still works however the problem is because our steps are not in the order in the applied steps box you're going to see nothing but the query still gives you the correct output point number five really really important the evaluation or the processing of the query starts from bottom up not top down please take a look what do i mean by that now that we have jumbled up all the steps you must be wondering that why and how is the query still working please take a look between the let and the in statements we declared a bunch of steps which are all jumbled but what we are calling as an output is nothing but removed rows right here now once the query starts to see that output removed rows the first thing it's going to do is going to start to find that where in all of these steps is remove throws remove columns actually so it finds the remove columns right here and then it understands that in order for me to evaluate remove columns i would need something like filtered rows as a step which is going to be an input right here then it goes and sets to find filtered rows which it finds it right here then it realizes that hey in order for me to evaluate this particular step i would need the source and then it goes and finds the source right here so power query is automatically going to do the hard work of finding out which step is placed where in the query and you don't really have to worry about it but when you're writing the query using the user interface the query obviously is created in the coherent order so that it becomes logical to take a look at the query and read the query but should you want you can also mess up the entire steps and the query is still going to work just fine the most important point here is to understand is that the evaluation of the query even if it is coherent is going to start from bottom up not top down all right demo time it's time for us to now write a very simple query on our own in the advanced editor without taking the help of the user interface so let's just start to define the query using the let in the in and all the fundamentals that we've learned and hopefully we should be able to create a query all right so like i said uh if you would want to define multiple steps in the query we definitely start with the let statement so i'm just going to call in a let statement here and let's just start by defining a list i will later in the video describe what exactly is a list but for now list is nothing but one columnar object structure anything that can only hold a column is nothing but a list so let's just say that my list and i'm not giving a space right here therefore i'm not really including this in the inverted commas or putting a pound sign at the start all right so i'm just going to say that my list is going to contain three values for power query to realize that what you're defining is a list you have to surround that in the curly brackets so i'm just going to start the curly bracket and maybe define three values the first value is going to be a the first the second value is going to be b and the third value can be one right so two alphabets and one number and because this is a number i have not really put uh that in the inverted commas all right pretty good the list has been defined now as soon as i want to go to the next step i can actually put a comma in here and start to move to the next row which is where i'll define my next step let's just say that in the next step maybe i want to count the number of items which i have in the first step but let's just actually do that in a while i'm just going to write the in statement in here i'm just going to call the list that i have created let's just take a look at what's the output that we get if i just maybe declare one single step right here the only mistake that i have done as of now is because this is the only step that we have there should not be a comma at the end i'm just gonna get rid of the comma let's just take a look at the output i'm gonna click on done right here and i get the list right here which is has which has a b and one go back to the advanced editor and start working there now what i want is i would want to count the number of items which are there in the list obviously i know that the number of items are three but let's just let power query do some work i'm gonna put a comma right here and i'm gonna start to declare another variable and i'm going to call this variable as list count and i'm going to use a simple function called list dot count so list dot count and i have to ensure that the capitalization is there power query is very case sensitive so i'm just going to say list.count and list.count is now asking you hey whis which list would you like to count so i'm just going to say that hey i have created a list which is nothing but my first step i'm just going to feed that right here which is nothing but my list and that's pretty okay now as an output i do not really want to have the first step which is my list as an output i instead want to have the count as an output so i'm just going to call list count as an output all right pretty good i'm just going to click on done and what i now have is two steps and the list count is nothing but three because we had three different values in here let's just add one more step to close this simple query i'm going to jump over to the advanced editor once again and what i'm going to do now is this maybe i would want to multiply the count with maybe any number maybe two so i'm just going to put in comma right here because i'm declaring the third step and i'm going to call this variable as multiply right and i'm going to say that hey i have a list dot list count as the previous steps name and i'm gonna multiply that with number two all right and as an output what i would want is the multiply as an output now take a look the first two steps do have a comma the last step doesn't have a comma and i'm just calling the last step as an output all right pretty good i'm just gonna click on done and what we get is six as an output and we can also now trace the three steps of the query that we have created the first step is a list the second step is the count and the third step is nothing but the multiplication that's how you would write a very very simple query in the advanced editor all right we finally jump on to part number three of this video which is understanding different object structures in power query what do i mean by that when you're working with the query and you're trying to match up your data trying to transform your data you'll end up working with different objects that you have in power query and what exactly are those objects how do you gain access to those objects how do you create these objects we'll discuss all of that now in particular i'd like to talk about five different very important objects which is nothing but tables we'll talk about lists we'll talk about records we'll talk about single or scalar values and the fifth one is we'll talk about functions let's start all right the first object structure that i'd like to speak about is nothing but a list very very important and highly highly useful what exactly is a list one minute definition list is nothing but a simple one columnar data structure in power query now don't confuse that with a one columnar table let me help you understand let's just say we have a three uh columnar query we have name age and score and let me just delete two columns of the three columns right here h is gone score is gone now that we have this particular column which is nothing but a single column this does not make it as a list although this is just a table with just one column but perhaps you can add more columns to this particular table right here in a list once that list has been defined you cannot have more columns you can have rows in the list but you cannot have more columns right all right what i'd like to now talk about is that how do you define a list in case you would want to define the list manually and how do you extract a list let's just take a look at how do you define a list first now let's just say that for example i need to create a list of three numbers and in order for me to define the list where i'm typing the values inside power query remember that list always is going to come inside the curly brackets so if i maybe go ahead and create a new step right here and in the new step i start the curly bracket inside of the curly bracket i write one comma two comma three i now have a simple list which does not have the column header because it can just contain one column and that's the only list that i have which is one two and three now remember that lists can only be defined manually when it contains a curly bracket now let's just talk about in case you would want to extract a list how do you extract a list from the data now what do i mean by extracting a list let me just give you two examples example number one what if i wanted to extract all the names of the students right here in a form of a list how do i do that now what i can do is as soon as you write the name of the column after the name of the table what you will get is a list let me help you understand as soon as i create a new step the new step actually mentions the source what is source source is nothing but the table which was there in the previous step right here the entire table with the three columns now this is a table as soon as you write the name of the column that you would want to extract from the table you would get a list so name of the table square brackets name of the column and if you now commit to this particular function this particular code that we have written what you're going to get is nothing but a list example number two you can also extract a list by using a formula altogether so let me help you understand one more example so take a look this particular data set has got three columns and maybe i would like to extract the names of the columns in a form of a list what is list one columnar data structure so i can just create another step right here and i can say something like table dot column names and i can start the bracket table dot column names is going to ask you hey you would want the names of the columns of which table so source is nothing but the name of the table it's actually a step which was like a variable which contain the entire previous steps table all right as soon as i now commit to this particular function i'm going to get the names of the columns but in the form of a list and there are many many ways to kind of extract the list but i'm just telling you a few ways to extract the list uh using a function and using a column as an output object structure number two which is nothing but a record let's just first understand what exactly is a record then we'll talk about how do you extract a record then we'll talk about how do you define a record in case you want to do that manually first things first what exactly is a record record is nothing but one row of data that's a record so this is nothing but the first record or the first row of data the second row of data record again a record a record a record and a record so we have six different records in this particular table right which is nothing but six rows of data now once you're taking a look at any particular record record just doesn't contain the values of the data or the values of the row it also contains the headers of that particular row so what do i mean by that when you're saying that this is the first record of data it just doesn't contain james and 21 and 66 it also contains the headers so that means names is james age is 21 and score is 66. so remember that a record is going to come along with two different parts one is going to be the value of the entire row and second is going to be what is the header of that particular row all right now how do we extract a record from a table in case you want to extract any record it's a very very simple way to extract it when you were extracting a column or a list we were writing the name of the column when we were extracting a record we'll just write the row number right simple enough i'm just going to create a new step right here in that particular step you can see that we have the reference of the previous table right here which is nothing but source and i would want to extract any particular record so all that i'm going to do is maybe write in the curly bracket i'm going to write the number two that means go extract row number two of the table that i have mentioned now when you take a look at this you're obviously going to assume that row number two is like this this is row number two because it's also marked as row number two but the counting in power query starts with zero and not one so this becomes the zeroth row this becomes the first row and this actually becomes the second row so what you're going to get as an output of this particular function is row number three not really row number two and now if i actually press enter you will see that we actually have got the record in a columnar fashion now it doesn't really give you the record in a row based structure it actually gives you like a columnar structure but that is still a row of the table now like i said the record has got two parts so take a look it not only has extracted the value tyler age 27 and score 63 but it has also got the relevant column headers right here so that's how you extract a record of any particular table now let's just take a look in case we want to define any particular record how do you define that what's the syntactical requirement there i'm just going to get rid of this navigation step right here and create a new step in this new step we'll start writing a record manually remember the two parts that we need to have in the record we need to have the value and the column header so now in case you want to define a record you start with a square bracket and the entire data the name of the column and the value of the column is going to be defined within a single square bracket so i'm going to say that the name is let's say chandi then i'm going to say that the let's say city is dubai and obviously this is going to be in the inverted commas because i'm writing text so i have now created two different column headers so name happens to be the column header then the value of that column city happens to be the column header and the value of that column now that's pretty okay and now as soon as i press on ok i get a similar structure like i got it last time which is nothing but a columnar structure which has got two parts the name of the column and the value of the column and this is nothing but one row of data all right the third object is nothing but a single or a scalar value in power query what exactly is that now the name suggests that a single value is nothing but one value that's it nothing that complicated it's just one value two four five apple chocolate are all just one single values right now the first thing that we'll understand is that we how do we extract one single value from a table or from a list and then we'll also talk about in case you want to define it how do you do that it's very very simple all right let's just talk about this so for now let's just say that i'm trying to get to this particular value which is nothing but 27 how do i get to that value now if you think about it this value is nothing but in the age column and it's in which row so it's actually in the zeroth row first row and second row of data so if i happen to write two inputs after the name of the table so i will write first is nothing but age age is going to give me the entire list and from that list i actually want the second row of data which is actually going to get me to row number 27 the value 27 let's just do that i'm going to click on the fx right here and i'm just going to start to write list after the name of the table so source is nothing but the name of the table and after that i will write the name of the column that i would want to extract the column that i'm looking for to extract is nothing but the age column i press enter and i get the entire list right here and from this list what do i want i want the second row right here so i'm just gonna write in the curly brackets i'm gonna write row number two and i'm gonna get the second value of this particular table and which is actually going to be a single value note that as soon as you create a single value output in the query the query is also going to change so here the query has changed to one two three that means the query is now giving you a single or a scalar value output now it's not a mandate for you to write the query this way so i can also get rid of this and if i still wanted to get to 27 i can just do the reverse of the process as well i can say that hey first i would like to get to row number two which is going to give me the record so i got the entire record and now from this entire record the column that i would want to extract is nothing but the h column and the column goes in the square bracket right here and this is the same way but we have kind of jumbled up the position of the row and the position of the column and we are still getting to nothing but a scalar value now how do you define it in case you want to manually define it it's very very simple let's just create a new step right here and you just write the value note that whenever you're defining a single or a scalar value any number is just going to be defined like this which is a thousand but if you're defining a text you'd have to probably write that in the inverted commas right so that's a text the last one which is nothing but functions in power query what exactly are functions functions are like pre-built capabilities that you have in power query which is like power query formulas that you can use to transform your objects which is nothing but tables lists or records whatever you're working with now there are like 800 plus functions available in power query you can take a look at all of the functions in the list or the documentation on the web but how do you actually get the list of the functions let's just do some work around functions and take a look at one or two functions in detail and i'm going to give you a very important piece of advice when you're trying to learn functions in power query now obviously one way to take a look at all the functions is to go to the web and take a look at the m documentation and take a look at all the functions but the other way to take a look at the functions is that you create a new step right here and then you just write hash and then you write shared once you actually do that you're going to get all the functions in a form of a record and you can click on the side of the function and you take a look at what exactly this function does so list dot range list remove items these are all functions that you have in power query and you can use these functions to transform or manipulate data the way that you would want all right there are like 800 plus functions right here and i can't really teach you all of them i don't even know all of them myself but here is a very important piece of advice that you can keep with you while you're trying to learn functions whenever you're trying to learn functions it's very very important to understand what is the input and the output of that particular function what goes inside of that function at different arguments and what comes outside of it it's going to be phenomenally important for you to understand please take a look now as soon i'll just give you an example i'm just going to cancel out this particular step and i'm just going to count the number of rows of this particular table that i have how many rows are in this table and obviously i can count that manually but i would want some function to do that now i'm going to write a function right here and i'm going to say that i would want to count the number of rows so now i understand that it happens to be a function which is called table dot row count and if i as soon as i start to um initiate the bracket of this function it's actually asking me that please give me a table for which you would like to count the number of rows the very important part to understand is that the input needs to be a table that means if you provide a list as an input if you provide a single number as an input it's obviously not going to work so if i happen to write let's say one right here it's obviously going to give me an error it's going to say that hey one is not really a table and i cannot really execute that particular function so you can see that one is not really a table right if i happen to write a list in this it's again not going to work so i have to provide the input parameter to exactly what the function is asking me for so i'm just going to maybe write something like source here and i know that source happens to be a table because it was having multiple rows and multiple columns in here i'm just going to close the bracket and press enter and then it gives me the output as 6. now 6 happens to be a single value output now i can just go ahead and multiply that with 2 and this is actually going to give me the answer as 12. now this gives me a single value output a single value output can be multiplied with a single number and that's the output that i get once you're learning functions it's very very important for you to focus on what is the input of the function is it a table is it a record is it a list what exactly is that and what's going to be out of that function so that you can start to use that output in some other places very very creatively all right that's been it i hope you found this video helpful although we did not really discuss any solution to any nasty power query problem but these building blocks of the m language are going to help you understand that how do people write the m code in the advanced editor a whole lot better let me know if you have any questions around this and i'll be glad to reply in the end i'd like to give a big shout out about my tax and my power query courses in case you're starting out with your journey with power bi and dax and power query are far too hard for you to understand you would want to master the fundamentals first and then you want to solve more challenging problems even of your own data i'd highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking around all this while and i will catch you guys in the next one cheers [Music] you

Channel: Goodly

Views: 36,863

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

Id: 5s8Ky5r43uI

Length: 25min 32sec (1532 seconds)

Published: Wed Jul 06 2022