Microsoft Excel: Learn M Language - Power Query Basics!

1. Welcome to the M Language Course!: Hi, welcome to learning the M language in Excel are three basis. Sorry, you're starting out in this M language. This is perfect for you. What you will learn from this course are to access the list and pumpkins using m functions with real examples, expressions, variables, and so much more. So let's learn the parkway and language together. See you inside. 2. Excel M Language - Intro: Hi, This is Bryan Hall and let's talk about m. What is m? So m is inside Power Query and it's the M formula language. So it actually stands for mashup because it really helps you when it comes to a mashing up your data, your data into something that's very useful to you. So what are the advantages of using m? So one of the questions is, why even butter learning about this language? One thing is if you've used Power Query, if you're familiar with the interface, right, you can already do a lot inside it, but there are a lot more possibilities if you learn how to use M as well. One of the common experiences that we can get across the web is if we need a solution that's written in M code, you can just copy paste code from the web, right? But when it comes to understanding the solution and customizing it according to your needs, then that is a completely different story. Okay? So our goal right now is to help you understand the building blocks of m so that you can understand the syntax behind this powerful language to make the most out of it. So one thing to take note of is the Query Editor. When you write a Power Query Query, write it generates M code. So when you try to translate the entire query into something more like a code like salt, that's the M code. That's what's happening under or behind the hood. So we'll be seeing later when we create our own query. And then you can see M code getting generated as well. So one thing to take note of is the formulas in M, There are completely case-sensitive, so you have to be very careful when it comes to naming your functions or you're naming your formulas. Because capitalization is very important and you will get syntax errors right away if you don't. So here's what we will cover four m. So to start things off, we'll be discussing about the m function list on how we can access the complete list inside Excel. And then we will be using these functions in action one-by-one. Okay, So we'll be having a couple of real life examples that you can use in your current query and then see those m functions in action. Afterwards, we'll be seeing generated M code. So whenever you create a new query inside Power Query will be seeing what's degenerated M code for this query. You'll be seeing like an inside look. And I will be discussing the EM structure in depth. So we'll be going from simple them expressions, two variables and two functions. The one on the right, actually this image over here, the code is actually one of the functions that you'll be writing. So stay tuned. 3. Enabling M in Excel Power Query: Now let's talk about in a being em inside Park re. Okay, So before we enable em, what we need to do is let's create a quick Park Re example first by trimming the names inside this table. OK, so what we will be doing, it's let me just select cell inside here. Let's go to data getting transform from table. And then it will bring us straight to the Park re editor. And what we will do now is let's see on how we can trim, OK, all of the names in here. So let's go to transform, Let's go to format And then let's Elektrim. Now We've already trimmed attacks so we could see, hear, smell on the steps. And the next thing right now is how do we enable m and C? What am code is inside? One thing we need to take note off its let's go first to the view tab, and you need to always enable the cruise settings over here. Okay, so if we disable this okay, you could see it disappear on the right side. That's re enable that. Okay. And you could see it over here. Another thing that you need always enabled if the formula bar. So make sure this is tick. Otherwise, you cannot see this formula bar over here, okay? And one of the good things with the formula bar, it's show few the specific code for that specific steps. So, for example, this one, you could see the code here for the trimming of text changing of type A swell. So okay, and then we have the loading off the source so I could see the cold of swell inside the formula bar. And to be able to see the complete M code, we need to go to the advance editor. So once we click that, you could actually see the code over here. But don't worry, OK? We'll be discussing the syntax and the structures. A swell. I am the subsequent video so that you can also understand the coat over here. Okay. And one of the things to take note off is the validation off. The M code is being shown at the bottom. So if you've done some programming before, I'm sure you're used to seeing the errors. Let's say, showing up here in a coat. But over here in the advance editor, it will be shown at the bottom. And one of the things is, for example, you could see here, right? The actual trimming is being done here. Okay, The am function for trimming. If this one So what you see or what you're doing inside the query Editor, the entire code is being shown here inside Advance Editor. 4. Finding the list of M functions: Now that we have a better understanding off what am functions are and how are they use. We can now explore the complete list off and functions, and there's actually documentation inside Excel that you can access very easy. Okay, so here's what we'll do is go to data you, Corey from other sources. Black White. Now it said, You're formerly bar, but you'll be typing in ISS equals okay, the hashtag and then shared I want to be press enter. It will show you all off the functions right in the AM library over here, and there's a lot right and one of them knowing thinks, though, is it's not exactly sort of so it's a pretty hard thing. If you want to look for trends, are what else could you do? So what we usually do is there's a cool trick that you can use to make this more searchable . Okay, so if you see here under convert into table, just click on it now converted to a table, and we can sort it in ascending order for the names, right, sort descending. And then let's say let's say, for example, we want to check on texts if there's a way for us to change it to. The upper case came to convert it to an opera case. So what we'll do is let's go to name since we want to work with text weaken, go for text filters, go for it. Begin Swit Okay, And then let's look for begins were tex dot and go OK And now I could see all of the functions that you can do with texts. Okay, Our strengths. And if we scroll down a bit right, you could look for Texas upper Okay, So if we explore this and just expand its a bit and you could see the documentation how it's used right Converting all characters to upper case and then we have sampled you say Sure swell on how it gets converted. Right, So we have a BCG over here, right and mixed case and now the output is in upper case, so it shows you both and it's available inside park. Or you could just access this any time you want converted to a table sorted out Hillary out just to look for us, but have functions that you need to click on the ones right, the function over here so that you can see the documentation for each one of them 5. Using M Functions : Now let us have some fun now with using the M functions that we want. Okay. So we can actually combine this with custom columns in Park three and then called and functions there directly so that we can have an immediate effect on our data. OK, so we have three sets over here Data, right. We have a list of dates. You have sentences, and then we have I ds. So what we want to do is for this one, okay? For the dates. We want to get the mutt off debates. Okay, Next one is we want to change this all into uppercase. Okay? And then the last one for the I DS is we want to pad the I DS with zeros at the beginning. OK, until it reaches to be eight digits. So it's gonna be something like 00012345 Ok, so we want to pad zeros in front. So the question right now is how do we go about this? So what we'll do right now is we want to use an function strictly so we won't be using the park re interface. So let's go to data. OK, I'm just enabled this first nuclear e right from other sources. Black worry. Let's explore the less off in function. So to do that equals okay, half tech shared, and then to make this more readable and searchable Converted table. Okay, Now let's soar despite ascending order. So the 1st 1 is the dates. Okay, We want to extract the month from it. So what we'll do is this Go for text filters, begins wit Kane and that its type in deep dot OK, because you want to look for a specific and function that works with dates. So if you go okay. Okay. Let's look for there's a lot in here, right? So, Data mutt. Okay, so let's have a look of data a month. Let's check the function. How do we use that? So for this month for the function, right? Daytime. Okay, so it only accepts one parameter. Just give it the daytime, and it will give you the mud component. Right? Okay. So pretty straightforward. So what we'll do is let me just close this. Let's just go for the name off shared over here so that we can reuse this later and then look for other functions. It's got close and load. Okay, Just leave that there. Let's go back to initial data. Let's go for the dates, right? And now we have a date that month. Let's use that to create and extract the months. Now Let's go to data from table that's loaded up in Park Creek. Once we have it here, let's go to add column. That's select custom column. If you still remember what the infection is, its date, not month. And what it accepts inside is the date. And thanks to checking out the documentation on how issues we know how to do this and let's go. Okay. And just like that, right, You're now able to extract off the month. So let me just name this marks. Well, okay. Said now we have the months. Lets go to home. Okay, so we have demand. Right? 9 10 11 toe four. So the cooling with this one is what we're dead, right? Yes. We didn't use the user interface of Park Re, but we went straight and created the custom column which invoke our am function. Okay, so now let's go to the next one. What they're going to do is to get the upper case right. Change this values over here are the tax over here into uppercase. So let's go back to shared. It's double click on it and let's look for the function right that will change our text into uppercase. So now let's go to text filters, right? Begin to it Now we want to play with text. So let's go to text over here. If we scroll down right, we have upper. Let's check the function so we have and then the text, right? And then just an optional culture. So we could just ignore this for now. So what we need to provide it's simply the Texan here. So you could see here, right? The text and that it returns back the text now in upper case. OK, so we just need want parameter so similar to what we did for the month for the dates. Let's go close. So remember that text that upper? So if you go back here to issue data, so let's get the data from this table, go to data from table. Right? So we have all the senators over here. Let's go to add column custom column now the function. The in function is text that upper. Okay, and then what? We want to pass it. It's the text. So let's just stop kick on the send assess column and then close it up, right? Validation is good. Go. OK, and now you have everything in uppercase making. So same thing that's just rename Arqam to upper case. And that's really cool, right? You're able to use to an functions to do whatever you want right now. Okay, lets go home clothes and load. And we're good with our second example. Okay, so let's work on our last example. So the question right now is how do we pad all of this? ID's right with zeros in front. So let's go again to our library. So now let's fill their this and let's look for text over here, okay? It's actually a text function that will give us the answers. So if we scroll down, it's actually text that Pat start over here so it's gonna be padding like at the start on how many characters that you need and then what character you specified. So let's check the function, the documentation. So just when this up, so what it takes in is the text, right that you want to change the count. Okay. And then the capture on what you want to pad in front. Okay, so if you read the definition, it returns tax value pattern to the let off count. Which means if we want the i d to be eight, right, the lead off eight or eight characters, but inserting spaces at the start off the text value. So which means if we don't specify the character that we want to pat, it's just gonna be panning spaces by default. So on optional character can be used to specify the character use for patting the default Pat Character is a space. In our case. We'll want spaces to be patted it fraud. But we want the number zero to be patted. Right? So which means we have three parameters texts, right? The count. How many characters? All you know, we just ate. And then the padded character we should so Okay, And just remember that this is tex number and texts. OK, so we have our text pad start ready, So we'll be using that. So let me just close this, OK? Maybe just discard this right now. Then let's go back to our initial data. We have our ID's table over here. Let's go to data from table. And that's do our magic. Right now. Let's go to add column. Custom column. Right. And our formula right now is text that pad start. Okay. And then what? We have ISTEA idea, right? We want the text to happen. And next s how many characters we wanted to be a Carter slum. So it's eight. And what is the pattern? Character in front. So that's the number zero. Okay, so no Centex shares looking good, Okay? But there's going to be one step that we missed. Once we execute this, okay, there's gonna be an heard. It's gonna happen. Let's go. OK, it's all errors. So the question is, why? So let's check this out. We cannot convert the value, want to treat for fun to type text. Okay, so what this means is, if we go back again to our custom column definition, it's because we're trying to pass in eyes over here, right to Texas, Pat start and text that Pat Start only works with text. And how is our? I just called to find It's actually defined. It's a number. So there's that conflict right now and what we will be doing it. Let's go to change time. Let's change this to text instead. Right? Okay. So replace Kurt. Right? So you want this to be a text condom instead of a number? So now it's a text over here. Mystified. Let's go to Adam called. And now it works. Fine, right? One of the cool things with Parker is we made a mistake, right? We could just go back to a previous step, make some fixes to it, and there's no need for us to re x are re implement that specific step. Okay, and now it's able to execute successfully. You could see here. Now it's tree zeros in front. Want to treat for five for this one? There's five years in front, one to a treat, right? So all of them are now eight characters long, and we have jurors pattern in front and which is really nice. Now let's go to home shows unload. And now you have all of the text. That pad start right. The output from your custom caught 6. Simple Expressions: Now let's talk about simple expression. So how do we start with your very first and codes? I hope you're excited. Okay, So let's start off with Excel. By opening a new black file came over here and then what we'll do ISS will be going straight to data getting transform new Cleary from other sources and then select a black re . OK, so we'll just be writing a query from scratch so that we can just play around with the M code. No need for source data or anything. So just click on it and we have our park where your editor window. So let's go to view and select advantage So I could see here, right? You could see the let L e. T. Over here to key Word, but it's actually not essential in starting your coat. Okay, so we can actually start with verse simple expression that you will be very familiar with. So let's just talk about this keywords later. Okay, let in later. Now let me just remove this and then that's type in five plus and you could see in the validation know syntax errors had been detected. That's got done and you get your 15 as a result. Okay, So you have now return your very first expression that yields a value. And this is the simplest code that he could have with him. Okay, so let me just go back to view that editor. So let itself. That's what you've seen a while ago. It's also an expression that produces a value. However, let lets us define intermediate expressions whose results are assigned to variables. So once we have it, let's go for an example. So I'll type and let. Okay, So what we have let what's gonna happen next is you want to specify some viable over here. Dividend, right. Let's say we want to do a simple division operation. Okay, so this typing device her six and go for end and then diffident, divided by device. Okay, So what we've done over here, we have some intermediate expressions that can be used. So we have our integrated expressions over here that can be used to produce the final value returned by the lead expression. Okay, So what we have is the viable is over here, right? And then we used this variables over here to produce our file result. Okay, so we have let and then you declare viable and you have in where this is the final result that you want to return or want to display. Okay, So the reason why we want to break down a large expression into this intermediate components, right with names assigned so that it makes our code easier to read. So, for example, since we have dividend device for it makes more sense to us on what we're trying to accomplish, right dividend about it by divider, which means you're trying to perform a simple division operation. Okay, so it looks a lot better. And one thing to notice when we have multiple variables right over here, we separate it by a comma. Okay, So since it's followed by another viable, we need to separate this by car. And another thing is, when I was typing this out a while ago, for the space is right. It's all up to you how you want to form it. What I'm doing right now is I'm just using tabs over here, right? So if I type this over here just to make my code look more readable So if you decide to make it look like this? That's perfectly fine. You could see on the validation that the bottom ISS there still no seductive error. So which means it's old good. It's looking good, but to make it easier to the eyes. And for example, if you write, it's cold. And then a few days later, you come back to this code and try to understand it again. If you put in the effort to make it look more readable than it will be easier for you the next time around once you need to review your coat. Okay, so what we do right now? Yes. Let's go done over here. And you could see that the result is fine, right? Because what we did just go back to the advance editor. Yes, we try to divide right attorney divided by six, which gives us five. So which means our quote is a working right were able to use viable. You could think of variables, have something like containers in a way, right. You're using the value turkey and that your specified get to be stored inside the dividend , viable, right, and then you're calling it again. Over here, it's like 30 divided by six. Okay, so to reiterate, in the first part of our let expression, variables are defied by name. Okay, so this to our names. And then it's followed by equal sign. Then expression producing the value to be assigned. Right. So the left side is the name right, which is like our container and then the right side iss, the one putting the value into the container. Okay, so don't forget, it's always separated by commas, and then the in part off the expression is simply the expression that defines what you're let will return. Okay, so this is what we're returning, so he could see over here. That's the final result that's returned to the park re window. OK, so take note off what we're seeing over here in the quarry sex right now. It's just create one. Now there's another way for us to write this. Just go back here and then what I'll do right now is at another viable over here, right? Just press tab, this type of result dividend bye bye device, for they could see what we're doing right now is its exact saying code that we have over here. So instead of having this cold right now. I'll change this and just delete this and let's make this to result. Okay, so this is a much more common the currents, right for the in portion to consist off a single viable. Okay, So both of them would produce the same result. That's what are coded a while ago and this code right now. So if I go done, you could see that it's still five, right? Because it's still the same. The only difference is we made the extra effort off using a new, viable starting to result in here, right, starting to co shit in here and then having it being returned. So the question right now is what is the benefit in doing this extra work? So you will see the result in the career settings in the graphical Korea editor. Okay, So the step by step right now, a while ago, right? You so just one step, it's just create what? But right now, there's multiple steps in here, right? So one of the main benefits of doing this is you can check for the errors because you have it now split and step by step. Okay, So if I click dividend, you could see its sturdy because that's the first step. If I go back here right dividend is now treated as the first step. Next step is divisor and then the last that is the result. Okay, so if I go to device, her could see its six. And I think I go to result 30 about it by six. It's now five. OK, so that's the cool thing. If you use a single viable to be returned and then you could see in the grafico editor on what? The step by step execution this. 7. Simple Expressions - Nested Expressions: So now let's talk about the next thing, which is about nesting. Okay, So when we say nesting, what I'll do over here isjust too quickly. Show you. So since the left expression is an expression that produces a value right over here, this one. Okay, let expressions can be used wherever values are expected. So you can mess this inside, other let expression. So it's like you could think of it like having a lead expression on top and then having another expression inside it. Okay, So what I'll do is so that I can just quickly demonstrate this to you. How did this okay for device her? I'm just press enter. And then what I'll do is how be defining another Let expression inside over here. Okay. So let me just type in X, right. It's a viable which has 10. And then why equals five. Okay. And then I'll define in okay. And then x minus. Why? So just remove this. Come over here, so you could no see, this is one viable right. And this one fire bullets. Well, it's just like a more complicated expression over here, but it's if you try to dissect it it looks fairly simple enough because let's just focus our eyes here for a second. What we're trying to do here is we're defining excess. 10. Why is five and then we're just subtracting right? 10 minus five. And then whatever the result, whatever the result here would be the value off device, sir. Okay, so which means device or is 10 minus five Would be five over here. Okay. And then, same thing when we go here, it's gonna be turning about about five. And we would have the result off six asked the result. Okay, so it's it's actually a complicated way of doing this. But what I wanted to show you is simply that you could define Let let expression a swell whenever you're expecting a value, right? So which means it's pretty cool. You could even add more on that expressions in here as you want. OK, so it could do that A swell. Now, if we go down, you could just see that the steps are still there, right? Dividend device. Aaron results. So if I go dividend, that's turkey for device, or that's 10 minus five. So that should give us five right and then. Actually, if you just open the form of the bars, well, you could see that full expression. It is the one the nest that let expression that we just dead. And it's a sign to divisor, right? It's really cool. And it's telling you that they were self this five. Now, if you go to result right dividend divided by device her and they were solved a six. Okay, so that's for our first M could for simple expressions and look forward to the next one. 8. Variables: Let's discuss all about viable. So for this one will be working with our cream example again. Okay, to show you some different types off viable. And then we'll be jumping over to a black spread ship afterwards. Okay, So, Ford, a stream example. No drill by now, let's go to data short, Maurice, over here. And then it's just double click on this creep, and then we'll have a look at our park for editor, and it's show the code over here. Advanced Editor. Okay, so this is the code, right? That's been created in the trim right off park free. And one thing you've notice If you've seen the M scripts that are generated by tools like for Microsoft Excel, you're probably notice that this variables have some sort of difference in tax. It looks a bit weird, right? With our previous examples off, the simple expressions what we have is just this viable name, something like this. But the jury didn't want over here, uses some sort off hashtag over here, right? And then you have the quotation marks. So what's the difference? Okay, so we'll be discussing this one by one. Okay. So what? We've been doing right now is we've been coding viable names. Ask what? Because regular identifiers and then the hashtag style names you could see over here that are used by the graphical interface Our cold coated editor fires. Okay, so what we'll do right now is let me just jump over to the black spreadsheet till they're just close death. So in our black special, let's go through data, get in, transform new Cleary from water sources, and let's create the Black Re. Okay, And then once we're in here, just go to view advance editor and let's just remove the code for now. Okay, So a regular identifier must start with eater letter or an underscore. So something like this. Okay, I could online. So if we have cold something that let okay, So we would have this as a valid, viable okay, Now, So it must start with either a letter or an underscore. So we could also write something like this. Mike Solo nine, right. 123 So that's also valid. So just let me just complete this, okay? So we could see over here, just removed the comma. You could see that there's no center. Ferris so that looks good. Okay, so it's either starting with the letter R underscore and then followed by additional Carter's so regular identifiers must not contain spaces. Okay. Oh, are certain utter special characters or M keywords inside. So, for example, images type in my excel space on the night is going to be invented. So it's going to show us that. Do you sit there? OK, and another one that's invented is if we type in one to a tree like that all night because it has to start Peter and underscore or Okay, so that's a regular editor fire. Now let's work for. So let me just remove this to our invented still images. Remove this too. Okay, So if we lived two comma and now everything's good to go now let's now discuss about I'll just add some additional spaces can see the next one, which is the code identifier, so a coded identifier can start with and contain any characters, including spaces, so it's a pretty cool way off. You're moving yourself from the restrictions off regularly fire. So what you need to do to create a code identifier ISS just need to hash tag right And then we have the coats. So inside the coats, right surrounding the code identifier syntax that normally has a special meaning in em, like he word, comment or whatever restrictions that we have is interpreted. It s literal texts, which means it's just treated as it ISS. There's no interpretation that's done to it. So we just want we can just write anything Freedy inside. What's inside the quotation marks. Okay, so what we'll do right now? Yes, I'm just type in a couple of examples, right? Mike's online over here, all right? And then they would just create another one. That's type and underscore My Excel online, and it still works. Fine. Okay, Now let's try the other examples that gave us in a Ramallah Go and let's write it asked a coded if I were so It's type in my XO space online can, and that's typing. Want to tree again and wrote the Kama. So I'm just saying that the viable name, my excellent eyes already defined and scope it's because it's treating this to ask the same . I'll explain this in a short well, so little do Islam. Just remove this, too, and now I could see that there snow sit there, Claire. So somehow, right. The space is now being accepted, and it's all thanks to decoded I did to fire. Now that's trying. Another example which resulted in an error. 123 My excel online. Okay, And now I could see no syntax errors because it's enclosing, decoded and in the fire, and it works just fine. Okay, so what is the reason why we have these two types, OK? Because it helps a lot in readability. Okay, So to demonstrate that that's type in this code, so I'll be creating this one again. Now type in this viral. This is a complex device, sir. Okay, so I'll be defining a nested expression in here. Que that in x minus Y Okay, so now we have two variables, and then I'll just defined the results. Dividend divided by this is a complex device, sir. Know him, and we're good for yourself. Okay, so this is just a division, and now let's go for it done. And you could see over here applied steps. Now this dividend. This is a complex device here, and that result it's not the readable right for this one. So what we'll do is let's go back to view advance editor. And now it's changed s regular to a coded identifier. OK, now, once we have a code identifier, now we can add spaces in over here, right? And then just to make it easier, I'll just copy this one and then let me just replaced this name with our code identifier. Yep. And now, once we have this, let's go. Done right. And now it's a lot more readable in our applied steps. Okay, so what do you notice a while ago is whatever name that we have here for a viable. That's pretty much the steps that's being showed over here. Okay, so another cool thing, it viable ISS. Let's go for this example over here. Okay. So, Dividend Vice Oracle six. Right? And then it's just type in the division. If I saw over here, then what we'll do is instead of typing and result, we could type it this way as a coda, Tentative fire. And this will work just fine because we are pertaining to the same viable. So if you go done right, it works perfectly fine. 32 about it by six would give us five 9. Functions: Now let's talk about functions. So the function is an expression that ultimately produces a value. So unlike the expressions that we looked at last time, OK, a function only produces a value when it is cold or monetise invoke. So it's more flight on the man. Okay, you define a function, okay? And then you call it later so that it does this work and then gives you bad. It's pretty hard to imagine it right now, So let's just go straight and create an example. Let's go to data, nuclear, e other sources and then black worry. Okay, so let's just go to view Advance editor, and it's just typing sample. Let's have this example s a function, right. So this is a function. There you go. Okay. So the parameters that are passed to a function disorder parameters OK, And then the function will reference the inputs, whatever this Passover, and then it will compute what to return. So what we have here, right? The function definition. It starts with the list off expected parameters. Which means it's telling you already you're telling this specific function is you can't accept to values over here, right? And then Once you have specified those fatties, just multiply them together and then that's what's going to be returned. OK, so just to repeat the functions definition starts with lists off expected parameters okay, inside a pair of currencies and had followed by this equal sign and that the greater sign it's like an arrow, right? And then followed by whatever the function body is on, whatever calculations that you want to do. So what we have here is a very simple function, right? It's just give it to numbers over here and that it will just multiply and then give back that result to you. Okay, so we can also define optional parameters. However, we will not be using this for now. Let's expand its a bit. Okay, so we can also write it this way. So you have dysfunction, and then you can now use let expression and then it say we have result right, this viable over here and then in, and then you give back the results. So what is going to do is whatever a B is in here, it's gonna execute this, save it in herself and then return back. The result asked the result off your function now. You can also nest functions inside. So let's remove this and create more are bigger example. So let's have let's create code where we wouldn't want to compute the disc up. So let's say that this contest 20% we should see her point, too. Right? And now let's define the formula. Okay, so I'm assigning this to the my formula, right? And then how do we define formula? Right. So we specified the parameters inside the Prentice's. So we have two numbers right then. Now let's start defining what we want to happen inside our formula. Okay, so that's go for a person, stage one might speed right? Because what we want to do is to get 80% and then we have discounted price equals a multiplied by person stage. Right. So what we're doing right now is whatever we pass here for a right, that's the original price, and then be it's the discount percent. H right. So we're gonna be deducting this from one more 100%. And whatever the presentation, ISS will multiply this with a Okay, Now it's return this now as this counted press. Okay, now let's go for result equals it's coal. My formula right now, Price this kind and result. Let's have a better idea on what has happened so far. So we defined just two variables. Over here we have the price. We have the discount. And then just to demonstrate how four minutes are created, we define our own formula wherein it accepts two parameters A and B. Okay, So a would be our price and then be would be our discount person page and what we're doing inside, right? This signifies the start of the function, what we're doing inside the function, right? It's just a simple calculation off the discounted price. So once you have the person Tasia just deducted from one like 100% minus 20% that would give us 80%. And now we would multiply 80% to the price, the original price. And now you have your discounted price. After you finish calculating, we just returned back the discounted price, right? And then that's the result off your my formula. And now, to demonstrate how we invoke or how we call our formula, we know create a result viable cold of my formula, right. And then pass in two variables, which is price in this case. So it's like passing 100 in here and in passing the discount 0.2 over here. And then the magic happens inside the formula, and the result would be back to the result viable Over here. Okay, So what we have right now is the question right now is what we're expecting. What value are we expecting? Okay, so the expected value is 20% discount off 100 would be 80. So if you go done, and now we can see the result asked Katie over here and there. The cool thing is, we can now look step by step price discount. Right. And then we have the my formula, which is your definition, right? This is the function that they have justified. And if you go to result, then we have the final value fading. Okay? 10. Functions - Reusable Functions: we also can do reusable functions. Okay, so which means we define the function in here. And then we call in a separate query that same function so that we can create reusable code over and over again. It's like you just define it once and then you could go in other quarries in multiple careers and then call the same function over and over again. So let's go back to you. Advanced Editor. That's changed the codes a lot. I'm going to do right now is just copy the function that we just created. OK to save some time. Let's go right Click copy Game. So to define a reusable function. I just wrote this and I'll just copy the function that we created a lot of go and what we'll do right now. If you just believe this right, and then that's the start of the function right Then let me just make it something mawr readable that's ruining the vibe. ALS are the parameters. So let's go discount over here. A change be to discount and in exchange A to press. Okay, now this is the function, the result of function that we just created Let's go done right. And then let's just give it a better name over here. Right on Cory Settings. Just give it the name off. Compute. Discounted price Now months we have. Yes, it's looking good at the moment. Let's go for Let's just save it. Now we have this reusable function created that has to go for new Cree. Other sources. Black worry. Okay, now this is where the magic starts to happen. It's go to view Advance editor. Okay, so let's go for result. Right? And since we defined a while ago this name, right, compute discounted price and it accepts to viable Cisco for 100. Let's try a 40% discount and then let's go for assault. If you go for done now, we can see 40% discount would be 60 and now you're able to use your defined function 11. Functions - Invoking: so when it comes to creating our own functions, one thing you can do is also to test it out by invoking it. So I'll show you a quick way on how to call the functions that we just created. So let's go to data getting transform. Sure, quarries. So once we have our functions over here, let me just double click on it. And then what I'll do is this is a simple multiplication function that we've created. So let me try five and four. Then you go OK, and you'll be able to invoke that so you could see here, right? It's now 20 and you could do the same, a swell for your other functions. 12. Functions - Passing Functions: Now let's talk about passing functions. So we're going to be discussing something that's very powerful. Okay, so we have the ability to pass a function into another function so the father function can implement a generic logic that's widely applicable. Then we use the function that's passed onto it to customize its behavior. It's pretty hard to grasp the concept in tourist so will jump straight into the example. Okay, So what we have here right now is less right off different functions that are already implemented in em. So this are widely available, ready for us to use. Okay. So don't worry on how I got to the screen at the moment. What I'll be doing later is I'll be showing you on how we can get a complete list off. I am functions. Okay, but for right now, just focus on this one, because what we want to do is just have a look at one function, okay? Just one function. It's table that odd caught up over here. OK, so this one So what? I want to show you. Yes. Let's just have a look at what you can do inside this specific function. So once I click here. It just shows me the documentation or the definition on how this is going to be used. Okay, so what? Iss the table At column function So it accepts a table. OK, 1st 1 over here and now it ads in the new column name, right, the text. Okay, so we have just specified the name. And then what's next? Is the column generator as a function? So what does this do? OK, so just ignore the optional parameter right now, so we have tree thanks to focus on the table, the colony, right, and then the function off the college aerator. So it's defined as at column named new column name to the table table. The values for the column, our computer using the specified Selection function column generator, which each row taken ass and input. So what this means ISS the table. That odd call will do the heavy work off adding a new column for you for your table, right? And then it just takes in the new column name. Whatever you specifying in here. Asked the column name and the cool thing ISS. It takes in a function that you can specify right and you can define whatever you want on how, like the contents for the new condom would be generated. Okay, so which means you can pass entirely your own making off a function. And then it just executed on each individual row in your table, right to generate your column. Okay, so that's for table That column, because that's what we're going to be using in showing on how do we pass function to another function? Right? Because the table that column is already on existing and function Okay, that we can use any time. Okay, So what we'll do is let's just close this and what we'll be doing right now is Let's just go for a new blank worry. Okay? You query from other sources. Black word. So what we will be doing right now is this. Go too few, Advance editor and let's define Let's define a new table OK from scratch. So let's create a fake table. Don't worry about this for now. And how will be defining this? Okay, so I'll just type it. And here first will be discussing separately on how do you define tables? But for now, for our demo purposes, we'll just need a table to be created. So let's just continue to create the contents off the table right now. Okay? So what I'll do right now is let me just remove this. Let expression. I just want to show you way. Have a valid table that's defined over here. Let's just go for it. Done. So what we're doing is we're pretty much defining two columns calling one column to write and in the content would be 5 10 for the first room, 15 20 for the second grow and 25 30 for the turd room. Okay, so now at least we have a working table to begin with. Sculpt a view. That's editor. Okay, now it's eight hour. Let expression right now, and we have source s this table, right? So we have our table to find in here. And now this is where we start passing over function to another one. Okay, now it's the fire. First, our column generator function. Because once we call the table that ad column What do we want to happen when that new column gets created? So what will we be doing? S let's just define dysfunction, right? So the private areas. The role. Okay. And then we have this one right, The arrow to define right what we want to happen for each individual rope. So what we're doing right now, this what we want to do is get the value right in column one and get the value from column two and an added up together and them both together. And now you have the value for your new column. Okay, so that's for dysfunction that we have to find. Now. It's now create this one. It's CO. He had column, right? And remember, we have tree things that we need to specify. Three parameters. First, it's the table. So we have a table defined and source, Which is this one over here, right? Nexus the colony. The nuke audibly. So, what? We want ISS. This name New Bro Total and then a function on Howard of value is going to be computed for the new Cottam. So what? We'll be doing this copy dysfunction right? And then I'll just pay sit over here and that's our function. And now we'll just return this back at column so that we can see our results because what we have right now is going to be magical. Let's go for it done. And now what we have is the new role total, which is five plus 10 which is 15 15 2035 25 sturdy, which is 55. Okay, so if we go back here, it's able to take in right whatever calculation that we want to happen or whatever operation that we want to happen. And it executed the table. That ad column executed it for each individual row, right? That's pretty cool. Okay, so if we go back again to our function, how do we define it? It's called him one. Get the family from calling one. Get the fat from calling to add them up together. And that's the new value, right? Calling one, because that's the name that we define. Called him to write at them up to better. And that's what your new role total called amiss. So to recap table, that ad column handles most of the work, which is adding a new Cottam, right, So this is doing most of the work over here and the beauty of it accepting our customer satisfaction. It allows us to specify what will be the contents off the new column so we don't have to write the function that handles all the responsibilities, officiated with adding a new column, Right? Because in this code over here, you could see that there's no such thing that we did. You just call this function. But what we only did Waas create a simple function over here. Okay, that takes in a single row and produces a single value, and that's what we passed over to our table that had caught up. 13. Functions - The Keyword Each: Now we've shown on how we can pass function to another function. So now let's look on how we can further simplify our M coat. We could change our code to look like this. Okay, So what I'm going to be doing right now, it's Let me just copy this, right? The definition off our column generator function, and I'll just pace it in here lyrically. And let me just delete this rover here. So what you could see over here is we have substituted the column greater function. So which shows that you can actually define the phone right inside. Okay, so no need for defining it outside and more. Now, if we have a function that accepts a single argument, okay, such It's this is this is a function that only needs one parameter. We can take it a step further to simplify this even more to make it even look better. Okay, so what I'll do is just use this as a playground over here. What we can do issue can actually replace row with the underscore, right? And it would look something like this. Okay, So what were just doing? Its simplifying the function over here right. And then there's a key word in em that's cold. Each you can actually was dis portion over here with the word which with a keyword each over here. OK, so now what we have yes, caught one plus que called him too, right? And if you notice right over here and in the last one, the less simplification that we can do it. You can just remove the underscore altogether. And it will look something like this, each condom one and then call him, too. And the beautiful thing with this oneness from dysfunction, right, that accepts a single parameter, you're now able to simplify it iss into something like this with the use off the each key where? Okay, so all of this transformations over here, they perform the same thing. But it's just more readable when you have this kind of format because it's a lot more pleasing to the ice. So what we'll do right now is that just copy this, okay? And I'll be deleting all of these examples over here and let's replace this function over here. Now it teach. And now you have coke. That is even more readable if you go done, and it's still working fine ass of this. Okay? We're still able to add the values to get her to get our new role total. 14. Thank you and Congratulations!: Thank you and congratulations on completing this course. So I hope that this has helped you in learning the M language inside our query. Okay, so you're also able to leave a review on this course simply by clicking on the reviews link over here. Once you have clicked on it, you can click on the live Review button to leave a review for this course. So simply by checking out their spot on. So please, please leave an honest review. I would love to hear your feedback. You can also check out my profile by clicking on my name, this link over here for more Microsoft related courses. So once you have clicked on the link, you simply see my profile page. And over here, you're able to continue your learning journey by going through my top microsoft courses.