Free M Code Class from Basic to Advanced: Power Query Excel & Power BI, Custom Functions 365 MECS 12

welcome to Microsoft 365 Excel the complete story and in video number 12 wait a second we've already studied lots of power query but guess what you don't know power query until you know M code [Music] M code is the case sensitive functional language behind everything we do in power query not only that but the next three mechs video are all going to be about power query once we learn M code then we can use lots of those tricks to learn how to import data better and use features like joins Group by and on pivot here's the list of topics we're going to cover in this video and the absolute most important things to M code is understanding values the lead expression M code lookup custom functions and the amazing each and underscore and don't forget about the PDF notes there are some parts of these notes with their diagrams that might even be better than the video now first things first of course what is power query well we've study this a lot it is a tool to import clean transform and load data Microsoft calls this tool data mashup that M and mashup is where they got the M when they decided to call this case sensitive function based language M code now luckily power query is in Excel and in power bi desktop it is not in power bi online although there's something similar called Data flows we will not cover data flows we'll cover power query now here's the full Microsoft M code specification guide if you want to go check that out it's got every single teeny detail about how M code works now in power query M code basically does one thing it delivers values and we'll come back to this full list here but this is the list of all 15 values these are the things that power query he can work with and that it can deliver and what makes power query and MCO different than other languages like worksheet formulas and Dax formulas in the data model is that M code is specifically designed to deal with things like tables records lists and something called binary which means we can actually work on files like Excel files and text files and of course if we're going to have a tool that does data mashup then of course we have to be able to work with these types of objects now we'll go through every single one of these values one by one but we'll do that a little bit later in the video now because power query is in Excel data ribbon tab getting transform data and queries and connection groups that's power query but it's also in power bi desktop data and queries that makes a power query now I have the tables that we're going to work with in two different files if you'd rather work in power bi desktop or over in Excel you can work with either one of these files now there are some slight differences and when there are I'll point that out now to open the power query editor we can go to get data launch power query editor and this is what the editor looks like in Excel it has one two three four different tabs over here in power bi desktop we go to queries transform data drop down transform data there are six tabs actually home transform add column view tools and help but this together makes up the power query editor over in power bi desktop now as we talked about a number of times before close and apply is different over here in power bi desktop close closes the editor apply loads this to the only place it can go the data model now for all of these queries I don't want to load them to the data model so I went to each query and right click unchecked enable load this is the same as over in Excel selecting only create connection and over here in the power bi desktop file I loaded all of the different table tables we're going to use over here in Excel on Excel table sheet we'll start our study of M code by importing this table and doing a simple transformation now for this video I'm going to work mostly over in Excel power query but if you want to work in the power bi desktop file here's how you get to the same table we're in home queries transform data transform data and I've already imported these tables so you can select the table here's the table we're going to be working with for a lot of this video and really the only difference you need to be aware of is when I talk about names applied step formula bars and advanced editor you have to realize that the advanced editor button is just in a slightly different location over here in power bi desktop over in Excel it's right about there in the home ribbon but I do want to show you one other difference I I imported most of these from Excel but I did not leave a connection I actually said hey copy the data into Power bi desktop and if you go to the source step click to open the formula bar and the source step is going to look a lot different than how we do it in Excel and what I did to create that first huge Source step is I clicked paste data into a blank table and after copying the Excel table I simply Ctrl Ved name the table and then click edit and that's what created this hard-coded Json file now later we'll definitely learn how to import a Json file not hard coding it but just like we have CSV text XML we also have Json file sources that we need to import sometimes all right I'm going to collapse this and you can work over here I'm going to jump back over to excel all now to get an Excel table into the power query editor we go up to get and transform and actually from table range brings in Excel tables spilled array formulas and defined names now instead of using this button there's a great keyboard find the right click key and then down at the bottom you can see that underline G so the keyboard is right click G this is the power query editor there are different tabs for each tab there's a bunch of commands on the left this is a list of all the queries in this Excel workbook file since we have day type sales selected this is the output for the query in the query setting pane this is the name of the query also known as the identifier these are the steps for this query in the applied steps list so far all we have is the automatic steps created these are also called identifiers and before we look at M code I want to add a new step and then we'll dive into M code now what we want to do is create a grouping report which will group workday weekday holiday and so on and then add the sales now that sounds like a pivot table but in power query and in the SQL database computer language we use the group by feature to group together records and then make some aggregate calculation or some other grouping calculation to group by a particular column we select the column go up to Home Group by or we can simply right click Group by in the group by dialog box when we select basic we get one column name which creates the unique list and one aggregate calculation we can also select Advanced and then have as many conditions as we would like and as many calculations as we'd like we'll leave it as basic that's the field that's going to create the unique list the name of the new column we'll call it total sales the operation on the grouped records is going to be sum and amongst those group records we only want to look and sum based on the sales column now when I click OK this is going to add a new step we have one two automatic steps and a step we created now there are three places that you can view and change the M code directly applied steps up in the formula bar an advanced Editor to see this in action here's group rows that's the name of the query step up in the formula bar that's the M code formula associated with that query step name and if we come up to Advanced editor this is where we can see all the M code in one place and sure enough up there's group rows there's the formula now we'll come back to exactly what this let statement means by the way it's very similar to the Excel worksheet let function we learned about two videos ago now let's change the M code using applied steps I'm going to click on group rows hit F2 and all we're going to do is change the name of the query step so I'm going to backspace and remove that space when I hit enter it's changed in applied steps nothing has changed in the formula but when we come up to Advanced editor sure enough that reflects the change now we want to talk about something really important for identifiers there's the identifier for the complete query and we have one two three query steps each with an identifier notice step three and one have no spaces but step number two has a space and why in the world in M code do we have to use a pound sign before the double quotes and then the identifier inside well the reason is because text already took up double quotes when you use double boats with some text inside power query just like the Excel worksheet interprets that as text so to distinguish an identifier from text you have to put a pound sign before the double quotes and the name however to make it easy on ourselves and make the M code easy to read for all of our identifiers whether they're query names or step names I'm going to click done select change type F2 and we want to rename it without a space I'm going to type add data types let's just make it easy on ourself and never use spaces now when I come up to Advanced editor that is much easier to read now let's click done another way we can use applied steps to change the M code is if we see a step with a gear icon that means there's a dialog box we can open up and change this step now let's select group rows and I want to look up in the formula bar here's our M code formula table dot group that's the function the first argument is acting on the previous step because we want to do some transformation to that previous step the second argument in list syntax and we'll learn all about list syntax coming up but that's the field where we grouped and then the next argument is a list within a list and you can see in this interior list there's the name of the calculation for each row in the grouped records there's the sum function and it even assigned a decimal or number data type now all we're doing right now is just noticing that's the automatic M code now let's come down click the gear icon and we want to change the M code using the dialog box let's click Advanced I want to add an aggregation we want to call this new column something like average sales we're going to use the aggregate function average and we're going to work on the sales column when I click OK there it is in list syntax we can see the three inputs for our second aggregate calculation there's the name of the field there's the calculation the data type is 1.2 or decimal I'm going to click at the end and hit enter I go up to Advanced editor and sure enough there's the new M code let's click done so we can use applied steps to change the M code but we can also directly type into the formula bar so I have group rows selected and I change my mind I don't want the title of the new column to be AV Dot I want it to be average so I change the M code in the formula bar click at the end and enter and sure enough the output from the query has changed if I come up to Advanced editor the M code up here changed and look at that I typed it in correctly so of course we can absolutely change the M code up here I'm going to backspace click done it's reflected there and one two three three different places where we can view and alter the M code now we want to talk about the let expression inside of power query so let's go up to Advanced editor and anytime you create a query and use the user interface it's always going to use this let expression now in video 10 we use the let worksheet function and we defined a variable name typed a comma and then the formula element type to comma we entered as many variables as we wanted and then at the bottom we created the calculation which is the output for the let function here in power query it's similar we start with let lowercase not only that but that's called a keyword things like let in type for data type you're not allowed to use those for anything except for the let statement to define the data type and so on so the let expression always starts with lowercase let and then there's the query step name or identifier an equal sign there's the formula and then there's a comma when you type a comma into the let statement it means it's going to deliver this as an intermediate step and we saw that over in applied steps where we could click back and forth between the different steps and see the output so we have a comma there's the identifier equal sign M code comma query step name equal sign and there's the M code but at the end of the let statement before we decide what we want the query to deliver the last step cannot have a comma when you do not type a comma it then expects for you to put in and the output now it's almost always whatever the last step is but you can put any one of the steps here and in fact you could type the number 43 or put any other query name but the let identifier equal sign M code a bunch of commas the last step doesn't have a comma in and then there's the output now another term we want to Define is the term expressions and an expression is any M code that delivers a power query value so for example this table dot group function is delivering a table so that's an expression this list is delivering a list which is one of the power query values so that's an expression when we have numbers in a math operator well that delivers a single number which is a power query value so that's an expression back over here with our let expression well of course even the let expression is an expression because it's delivering a power query value in this case the result of table dot Group which is a table now one last thing just like the let function that we created in the worksheet any variable that we create can only be used inside the let expression however we can certainly pull other queries into the let statement click done now from page 10 in our PDF notes here's your cheat sheet for the lat expression lowercase let variable name equals m code if you do a space you're going to have to get fancy with that syntax variable name equals m code all the way to the end no comma in and output and the output is almost always the last variable but it can be anything now we just learned how the let expression is automatically created when we create any query later we'll see that the let expression can be be used in any M code to Define a variable that you need to use but in either case this is your cheat sheet for let now we got to talk about what happens when we insert a step into the applied steps if I select source and insert a step right here one of two things happens either that step works perfectly with the remaining steps in the query or it causes errors now let's look at two examples I have Source selected I want to come up and rename this so I double click type of day and enter we can see table dot rename columns but sure enough the subsequent steps already have the previous column name and both of these steps are using it now in a situation like this we have a few options we can actually edit the M code in both of the steps and when we do that there it's working or probably a better option if I re-edit this is to not put the step here but put it at the end now we can certainly insert steps and delete steps when we delete steps the same scenario arises if we delete this is it going to hurt the remaining steps in this case it's not so I click the red X delete come to the last step I can double click and type of and enter and sure enough that is working now let's delete this step and I want to look at the second scenario here's a step and we definitely have a gear icon which means I can click it and by using the group by dialog box it automatically writes the M code for the table dot group function but the thing is we're only using three of the arguments and if you use this dialog box it only allows you to change three of the arguments and we want to use the fourth argument in table dot group so that means we're going to have to manually type the code we cannot use this dialog box now I'm going to click OK and we're going to change the M code and then that will require that we insert a new step now we're going to learn something about the gear icons and the default settings if I come up to table.group and actually if you look over here what table dot group does by default is it takes all of the work days groups them together and then gives you some aggregate calculation but we can use the fourth argument in table dot group and instead of taking all the work days it only takes consecutive days and sometimes that's exactly what you want now I want to come to the end and when I type a comma the screen tip pops up and it says group kind but it doesn't give us any real good hints about what this does now in a moment I'll show you how to search online or internally inside the power query Editor to get help with arguments like this Now the default is what we have here but if we put a zero into group kind then when I hit enter it groups by consecutive occurrences now when we Group by consecutive occurrences that actually is depending on the date column sort so if someone comes over here and sorts this table biggest to smallest that will wreck this report so what do we want to do we want to insert a step but before we insert a step here I want to go learn something important about the let statement and M code Advanced editor and notice the step group rows is using table.group but this line of M code is trying to transform or change the previous step somehow so almost always the first argument of whatever M code function we're using or just straight M code has the name of the previous step and the cool thing is when we insert a step right here this line of M code will update it will no longer be looking at add data types it will be looking at our sort step click cancel so I click the step above and I want to make sure that the date column is always sorted A to Z I definitely want to insert a step right there click insert and so now our second example of inserting it's not going to hurt any of the subsequent steps in fact it's going to help now that we've inserted a step when we go up to the advanced editor the M code has adjusted table dot group records is now looking at the previous step table dot sort is also looking at the previous step click cancel now we'll come back and talk a lot more about table.group function two videos ahead when we talk all about group by now one thing about M code is there are over 700 built-in functions so a lot of times we have to go out and search for information about a particular function and one way to get information about functions is to go to Google and search table.group and we might put Power query to make sure it gets the M code and sure enough there it is it describes the five different arguments and it has some notes here that are actually not very good but it does mention group kind dot local that's what we actually did when we put a zero to group by consecutive records so we can search by groupkind dot local back in Google I'm going to search for group kind dot local there it is and sure enough this is a much better description than we saw in either of the other two places group kind.global that's the default group kind.local that's what we used with the zero a local grouping is formed from a consecutive sequence of rows which is exactly what we wanted now let's jump back over to the power query editor and searching through Google to get directly to Microsoft site is great but here's a great trick I have the last step selected and temporarily I want to insert a step because I want to get some information about a function when you insert a step it says custom one it also automatically thinks you want to do something to the previous step but I don't want to I'm going to type table dot group and in telesense inside of power query is horrible if I had hit enter there it would have put the wrong thing in so I'm going to click at the beginning and enter and sure enough here's a bunch of help about table dot group another great trick for searching for whatever you might want inside a power query we can type lowercase shared actually it has a pound before it and when we do that it turns red because when we hit enter it gives us a record with over 700 entries if we come up notice it says records if we convert this to a table we can then search through the entire column let's say we want to look for group items and there it is group kind dot Global local table dot group even a fuzzy one all right I'm going to click escape on custom one right click delete until end delete now I did the same thing over on our power bi desktop file and whereas there's 837 standard library items over in Excel over here in power bi desktop there's 1082 and in large part that's due because power bi desktop has a lot more connectors to external data sources like these Google connectors here all right with all of that background M code knowledge now we want to talk about the heart of M code which is all the values that power query can deliver there are 15 different values now if you you're going to work on the power bi desktop power query file select this query with this step selected over in Excel we'll do the same thing with the group rows selected now here's the crazy thing we're actually going to just use this query and create a bunch of steps delete steps just play around over here experimenting with values now we'll look at null logical text and number first now these types of values are usually imported as part of a data set or we type them directly into formulas but I want to add some steps here and show you what is called a literal that's how we hard code the value into the M code I want to insert a step up in the formula bar we'll start with n-u-l-l that's null that's the absence of data it is like an empty cell in Excel now right now if I hit enter it puts this in the in tele sense in power query is horrible so I'll move my cursor to the beginning and enter click the f of x highlight and watch this I'm going to type lowercase true that's how it appears in the M code but when I enter it shows the Boolean value capitalized f of x and we'll type some text and enter f of x and I'm going to type a number enter I'll rename these now let's go up to Advanced editor it automatically put in the syntax that says this is an identifier now watch this because power query is case sensitive if I type Capital null that is not one of the keywords also notice that although true and false appear capitalize in the M code you always have to enter it lower case either true or false number that's a keyword but because I have it capitalized it works as an identifier now I'm going to put a V at the each one of these to keep this convention V is for Value those are the first four values I'm going to click done and of course it's polite it's reminding me that number wasn't recognized when I click Advanced editor sure enough the last step here I renamed it but I forgot to come to after in and fix that name now I can click done now five to nine time date date time date time zone and duration how we enter them as hard-coded values or as literals is much different than over in Excel I'm going to click f of x and up in the formula bar we do pound time and lowercase and then there's three arguments hours comma minutes comma seconds and now when I hit enter whoa it shows exactly as the time value should now look at that just like we saw the true capitalize that's different here but if we come up to Advanced editor that's the hard-coded M code that's required click done click f of x now for date that's how we enter it and it's going to be year month and day but there's something really exciting about power query as compared to the Excel worksheet and Dax we can go from January 1st zero zero zero one all the way to December 31st 9999 so I'm going to type the year 1598 comma January comma the 15th and when I hit enter sure enough that's going to be recognized as a date historians have long been not happy with the Excel worksheet because you couldn't put historical dates in but in power query you can definitely do that f of x there's date time with year month day hour minutes and seconds so when I enter this there's the date time hit f of x now this is date time zone and it's going to represent the Universal Coordinated Time and in this case we have everything that's in date time but this is an offset either positive or negative hours and minutes so when I hit enter this will say plus 9 and sure enough there it is the last one is exciting this is duration this is just an amount of time where we have days hours minutes and seconds but when I hit enter there's one day one hour and 10 minutes if I rename and go up to Advanced editor here's the first nine values we're going to look at and each one of these values delivers a single item the remaining values we'll look at like table record list those all contain multiple different items now we looked at how to hard code the first nine power query values into M code and much of the time we'll be getting these values as part of a data set in a table now we want to go look at a couple examples of how we might use these first nine values in M code now I click done on the advanced editor and I want to select the last step and we want to learn something really cool about these variables in a let statement I can pull group rows down below here and just leave these as intermediate steps that were not necessarily going to use anywhere else so I select the last step come up to f of x type in the formula bar grouped rows and enter if we look at the advanced editor even though we created this step up here it's just a variable and we're using it down here that's true for any of these variables we can use them anywhere throughout the let statement done let's add a new column so I go to add column and I want to add a custom column and we're going to name this new column workday sales greater than or equal to three thousand and what we want to do is I want to create a formula here that uses the first four values and I want to use the IF function or if expression and I want to ask the question for each row is type day equal to workday and our total sales greater than or equal to 3 000. now the way we do if is much different than in Excel we type lowercase if no open parentheses we want to access the item for each row o in type day so I come over to available columns double click those square brackets are called field access operators because for each row it's going to pull the correct row item and we type equals 2 and the first Power query value we're going to use is text workday in double quotes and we want to run an and logical test we don't use the and function we use lowercase and then we want to ask the question of total sales ask are you greater than or equal to and now we'll use our second power query value on number and we type A Space in Excel in the IF function and dax2 we type a comma and this would be value if true but here in M code we say then and we want a Boolean value so lowercase true then in Excel and Dax we type comma value if false here we do else and I want no no close parentheses so we have values text number Boolean and null click OK and that is working now let's make this m code even more efficient notice I pulled group rows down and then built this formula which is looking at the previous step but I don't need to do that I can just build this step and point this first argument directly to group rows I see it there so I hit tab enter now I can come down and X this step out delete I'll rename the step and go to home Advanced editor and sure enough there's our last step table dot add columns is looking directly up at group rows it's totally allowed to do that now another great trick in power query and M code is you can actually copy and paste this m code wherever you'd like I'm actually going to copy everything from the Second Step all the way down to the bottom Ctrl C if I had selected everything including the top cancel you could come over and right click new query other sources blank query it starts out here like we're going to type something but you come up to Advanced editor highlight and paste but that's not what we want to do here now if I want to close the editor I have not loaded this yet so I have to come up to close and load close and load two only create a connection and I want to make a parallel to power bi desktop power query over there you're allowed to close the editor without actually applying any changes so if we were over there we wouldn't have to actually load it as a connection only and I'll show you that in just a second but now our queries there we can come back later if we had the full let statement and we wanted to paste it into a new query here in the Excel file get data from other sources and there it is blank query over here in power bi desktop power query we have day type sales and a bunch of you are working over here so you have all these steps but I don't have them all and I just want to copy them from Excel so I go up to Advanced editor and I've saved everything from the second step down we can see our source is hard coded Json over in Excel our source was an Excel table so I'm simply going to highlight and both queries start with source as the first variable or step so I can control V and this step except for the tab which I'm going to do right here it's already looking at source all the rest of the steps will work I click done there they are now in Excel if you want to close the power query editor you have to load it somewhere you can't just close it and come back later but in power bi desktop power query we can simply use the close button and it will close just the editor so I'm going to select close everything is still saved I go back up here transform data it's all still there and here in power bi desktop the file if I want to get rid of it I can otherwise I can apply the changes that's actually a big difference between power query and power bi desktop and over in Excel I'm going to click apply changes now our next step is I want to look at some date and duration examples I already actually imported this table as a connection only so over here in Excel we can just open the editor and start working but in power bi desktop I haven't loaded this yet but I want to show you how to copy and paste data into Power bi desktop because over here in Excel we have the freedom of a worksheet we can store data here we can do anything we want now over in power bi desktop the anything we want in the worksheet that's the a part we can't do over there but we can copy and paste data so I'm going to copy this and here it is in power bi desktop paste data into a blank table I click I use control V to paste there it is I name the table click edit come over right click uncheck enable load so those of you using this file can now work over here in Excel I'm going to double click duration example now the first example of working with dates is awesome because over in Excel we don't have dates before January 1st 1900 now that date right there is January 15th 1598 and I actually have no idea how to show the full year but in some cases when we're building date tables and date attributes we need to extract from a date the year so I select the date go up to add column and there's all sorts of amazing ad column commands for date time and duration I'm simply going to say year and you got to be kidding me 1598 and I have this set to zero zero zero one so that's the year one now when we're dealing with dates and times and durations power query doesn't really let us do things like subtract two dates to get number of days and the reason why is this is a date value and number of days is a whole number but over in Excel we could simply subtract the two dates and remove the number formatting and the number would be revealed over here in power query if we take n minus start we get a duration not the number of days like we're after that's not a problem because there's a bunch of functions that can take the result as a duration and convert it back to a number also if we take end and add a number of days in Excel we can do that calculation directly but here these are different different values so we're going to have to use a special function to deal with the different data values and deliver what we want a new end date so anytime you're making calculations across different Power query values or different Power query data types you may not always get what you want but almost always there's some function to come to the rescue now the first thing we want to do is take n minus start and we want to see that in fact this will give us a duration new column custom column I'm going to call it number of days NF and we'll see Y and F in just a moment let's just take n minus start click OK and it sort of gives us what we want but this is a duration and only the first part the number of days are showing let's click the gear icon now there's a great function called number Dot from and we have a duration and we need to get it to a number sometimes you have text numbers that need to go to a number or dates or times so we type number Dot from and whatever it might be if it can convert it to a number it will click OK and sure enough there's the number of days now up in the formula bar table dot add column it's acting on the previous step there's the name of the new column for each row there's our formula there are three arguments there we want to go to the fourth one actually before we go to the fourth one since I don't really remember what the data Type M code is for whole number I'm going to go cheat go back to insert ear and it's in 64 DOT type now the funny thing is most of the time for defining a data type we use the keyword type and then something like date for date time for time number for decimal but we have to remember this come down to add custom and we do want to do it in the fourth argument here rather than adding an extra step so I click in the formula bar comma I'm going to type int I see it right there tab click at the end and enter so we used number Dot from to coerce from a duration just the number we want and added a data type now there's an easier way to do this if we really want to take the difference between two dates but learning about number Dot from is important I'm going to select end and order in how I select the columns matter so I'm going to select start second add column and over in the add column from date and time group there's a bunch of awesome automatic formula options so we say subtract days and sure enough table dot add column previous steps subtraction we want to change that but look at this each and duration dot day so this is a very specific function to take the duration and convert it to days it also put in the int 64 DOT type I'm going to double click the second argument number of days DD for duration dot days enter by the way just for a second I'm going to cheat up here if I type duration there's a huge list of duration functions that allow us to extract different types of time related data from a duration backspace and enter now the next calculation we want to do is we want to take end add some days and get a new end date add column custom column we'll use new end date as the name and we want to look at date and look at this we can add all sorts of different things we want the first one date dot add days two arguments we'll take date comma number of days there it is close parentheses click ok and there we have from the very first day in history we're going to add 12 days and get 11301 up in the formula bar comma and this is where we use the keyword because most data types just require this keyword and then you have to know what the M code is I'm going to type date enter all right so the main thing we want to remember about different M code values especially when we're doing lots of different date and time calculations is if you're doing calculations on different data values you got to be careful probably you got to look for a function that can help you all right so the first nine values we talked about no all the way to duration those are single bits of data but now we want to talk about the power query M code values that contain multiple bits of data we want to talk about table record and list let's go over to the query tables in column now to follow along with this section of the video as we learned earlier in the class the query were about to use is pointing to an on-premise folder so in Excel you have to go to data source settings select the folder change Source browse and you point power query to the video number 12 folder that you downloaded and unzipped click open okay close in power bi desktop you go to transform data and there it is data source settings and the rest of the steps are the same now in this class so far we've had many tables with columns and in each row of this column there is a table and of course one of the amazing things about power query that none of our other tools can do is we can actually have a column of tables and then use power query to transform them however we would like now I imported these tables and these are officially table values I want to go over to M code values literal this is a query I made and in applied steps I want to select table click the gear icon because guess what that's how we hard code a table into M code we start with pound table open parentheses and in the first argument in list syntax and we'll learn about list syntax in just a second list syntax just means I have an ordered sequence of values these are the field names and they have to be in double quotes so we have two fields in this table the second argument lists the records and this is is called a list within a list because the first list the outer list it needs to know the ordered sequence of Records record one record two but because each record has multiple items we need a second list that's the element for the field boom product that's the element for the field sales all right so that's how you hard code a table now I don't think I've ever hard coded a table in any of my data analysis Solutions but you might imagine a situation where you have a small table of data maybe something lookup that you want a hard code I'm going to click cancel go back to tables in column so table values very common in data analysis because tables are where we store data now earlier in this class a number of times we've had Columns of tables and we combined them remember if we're going to combine these tables each table in each row has to have the same structure syntax and field names next video we'll learn what to do if that's not the case but that is the case here now we've combined these tables a few different ways but I want to show you a slightly different way now notice this is a table it has a single column we want to insert a step so I click f of x we're going to use that table to extract all the tables and combine them but just for a moment I want to look at the table dot combine function and read the screen tip it says please give me tables as a list this is not a list it is a table with a single column now we haven't learned how to do M code lookup yet we'll learn later in this video but if you have a table and you know the name of the field we can extract or look up that column and return it as a list using square brackets now square brackets in this context are called field access operators later we'll see that there's some other uses for square brackets but if we type the name of the field inside our field access operators and that goes after a table in fact let me just delete this and prove that when I hit enter it returns it as a list and that's what table doc combine requires so we'll put it back inside of table dot combine close parentheses and when I hit enter bam we have combined all those tables into a single table all right so we are going to encounter a lot of power query table values now the next M code value we want to talk about is a record and a record is whatever row we have in a table now for our first example of a record I want to add a column to this table and extract each record into a separate column so I go up to custom column I'm going to call this record and there's an amazing bit of M code that allows us to extract a record from a table in a custom column and it's underscore if I click OK there it is a record in each row Aspen 34 Quad 500 and a record always appears with the field names and then whatever bits of data we have for each field name now if you look up to the formula bar here's table dot add column custom which is the previous step we'll rename all those later there's the name and this is the formula we're running in each row now each and underscore are syntactical shorthand for a custom function we'll learn all about custom functions later in this video you can think of each as hey I just need to do something in each row and underscore you can think of it as please just give me the whole record now for our second example I want to go over to M code values over to applied steps and there it is record I want to click the gear icon and this is how you hard code a record in m code now this is the second place we've seen square brackets we're using square brackets to define a record and when you're using square brackets like this the field name inside does not need to be in double quotes and it doesn't need that double quote and pound sign but inside square brackets we separate field name and data with commas so here we have two field names and two bits of data now again hard coding a value like this or creating a literal like this I don't do it too often but we definitely want to check it out and see it one place you'll see this is in Microsoft help for M code sometimes when they're dealing with records or 10 tables they'll show records this way all right I'm going to click cancel go back over to tables in column now the next M code value we want to talk about is a list let's add a new step and up in the formula bar we're going to type curly brackets one comma 2 comma 3 close curly brackets now it sort of looks like array syntax over in Excel but over here in power query a list is defined as an ordered sequence of values housed in curly brackets but there it is there's a list when I hit enter there's my list one two three now it is different than the array syntax over in Excel because the array syntax in Excel can have rows and columns we only have an ordered sequence of items in a list I'm going to add another step and lists are very versatile I can have a number comma text comma I could have a list within a list and in fact I can put any of the M code values including tables records and so on into a list when I hit enter now I can see down here number text list I can even click and see the list now probably the two best list tricks over here and M code are these curly bracket one dot dot four three and curly bracket this is equivalent to using the sequence function in the worksheet and telling it I want 43 rows when I hit enter that is beautiful 1 to 43 as a list but here's something sequence can't do at least not easily curly brackets I want to go from a and notice it's text dot dot quote M get rid of that extra quote and enter her that is amazing that's an amazing trick for lists so we can do this with letters we can do it with numbers of course we can just type our numbers and we definitely can have whatever different type of power query values we want in a list now lists are very important in power query M code actually as we've seen a bunch already let's go back to day type sales I want to go to grouping rows here's table dot group acting on the previous step well there's a list but it only has a single item and in the third argument this is a list within a list and it defines the two aggregate calculations and what's so nice is the outer list allows us to have multiple aggregate calculations but within each aggregate calculation we want to be able to define the name of the column the calculation for each row and the data type we can also see with this example of a list why we're allowed to have different M code values in a list because in order to get all the details for this aggregate calculation we need a text field name a function and a data type also although a data type which we've been throughout the class using the icon at the top of the column to define a data type that sort of doesn't seem like a value but it is this list right here requires M code values and because we want to be able to add a data type to an aggregate calculation we're lucky that data type is defined as a value so a list within a list very handy the question is why is it that sometimes in power query M code I see a single item in a list well anytime you see a single item in a list in an argument you know that that argument accepts multiple items and since this is the grouping argument for table dot group it does allow more than one field for grouping enter and right inside this formula is another reason we have to understand lists all aggregate calculations like average sum require the number refers to be in a list that's why every aggregate function that you look up will always say list Dot and then whatever the calculation is now one last quick example about lists here I have this step selected I'm going to click f of x insert a step temporarily we're going to use our field access operators to access total sales when we use our field access operators to go and get a particular column from a table when I hit enter it always returns it as a list and this is the form that numbers need when you do some sort of aggregate calculation and they always start with list and I'm going to do list.sum and notice the argument says hey I need it as a list and so that would just add up all of those and give us the grand total now I'm going to X this out delete all right let's go back to tables in column so in M code we're definitely allowed table values record values and lists now the last three M code values our binary function and type binary represents a sequence of bytes we've seen a bunch of binary items in columns like Excel files CSV and text files functions those are incredible but that's two sections ahead in the video where we will Define and create awesome custom functions and as we just saw type is a value in power query that is the data type that we assign to a particular value now we want to be careful not to confuse M code data types with M code values now there is some overlap time is a value it's also a data type but data types are used on fields or columns to make sure we have consistent data in the column and that we can make the calculations we want in the load location M code values are just the possible output when we use M code for example if we have the M code value number and we apply a data type decimal number currency or whole number the result that we load will be different than the original data source now let's go look at an example in power bi desktop here's the query and I actually loaded this to the data model here in Excel this is the source data I've already loaded this let's open the query and we can see the source but if we change the data type to whole number replace and then load the output and any formulas or calculations pointing to that data will change now this is permanent as long as is we don't change the data type in the query and we can actually change it back to decimal because the source still has the decimals if I open this up data type decimal replace load sure enough the output changes as another example if I change this mistakenly to data type text replace load when I want to add alt equals this data type is going to prevent the sum function from adding Ctrl z z that control Z out here in the worksheet actually change the line of code up in power query now our next Topic in our M code Adventure is well before I tell you what topic it is I have two questions for you I want to import this into the power query editor so I'm going to use our right click key G and my first question is this if I want to drill down to extract this I go right click and drill down question number one what in the world does that M code mean question number two I want to come up to the source step and what in the world does that M code mean both of those are examples of M code lookup now before we see the Syntax for M code lookup those of you that know the index function you already know how to do M code lookup because check this out the index function takes a table then it says hey I need to know what row you want and column you want it says third row second column and it Returns the intersecting value now if we were doing this in power query M code and hard coding the row numbers in this would be called row index lookup however most of the time we want to be dynamic and point to an input cell using the match function that takes this value and dynamically determines a row when we jump over over to M code when we do a dynamic lookup it's called key match lookup now over here in Excel of course we can look up a single value we can also look up an entire row and we can look up an entire column now straight from page 13 in our PDF notes here's the diagram for M code row index lookup and key match lookup now for row index lookup this is where we hard code the number into the formula we start with the table that's the identifier name for the table then in curly brackets called row positional index operator we put the row index number now get this power query is base zero so Row one is zero row two is one row three is two and so on WE hard code the number into curly brackets that will get us the row or the record then we use square brackets called field access operators with the field name inside that determines the column position together we have a table a row position a field position and Bam we get a two-way lookup notice the similarity to index we have a table then a row then a column now when we do row index lookup it's not Dynamic to do a dynamic lookup we'll use key match lookup start with a table identifier curly brackets to get the row position and then inside we use square brackets to do the dynamic lookup and these square brackets are called the lookup operator inside the lookup operator we have a field name and because we're putting a field name in square brackets you don't have to put double quotes the pound symbol and you can have a space field name equal and then whatever the lookup value is now one thing that's different about M code lookup is that unlike the Excel worksheet we can't directly do approximate match lookup we can only do exact match lookup M code and Dax in the data model both have a hard time doing approximate match lookup however we'll definitely see a way to do it later in this video but for exact match lookup we can use this method to dynamically determine the row then following the row we have our field access operator field name and that determines the column position all right we have row index lookup and key match lookup we can extend these lookup techniques to look up just the row we can also look up a list from a field we can look up just the field and in fact we can even look up multiple Fields simultaneously all right we're back in our lookup example I removed that last step and rename this step we want to try some lookup we're going to go up to the f of x it will repeat the previous step name and it is a table let's first see if we can look up a record that means we use our positional index operator and if I want to look up the first row which means this record right here power query is base zero so I type a zero when I hit enter there's a record that's our first lookup if we go back to our table sure enough that's the first record but remember we hard coded that number in if I come over and sort ascending I'm definitely going to insert a step now when I come back over here I've hard coded the first row in so it gets the first row which happens to be Aspen now I'm going to delete the sort rows and we have the row but now from that record or row we can use our field access operator if I type products and enter now I've done a two-way lookup table row column now let's get rid of this enter and see if we can try a key match lookup so positional index operator lookup operator and I need to type products that's the name of the field are you equal to and we'll look up quad right now it'll just get the record and enter now we'll go back to the previous step a table we'll sort ascending click insert now when we go back it's still going to see quad that is dynamic no matter which row quad is in it'll retrieve that record we'll delete sort delete and now we can look up retail price and enter now we want to learn something important about key match lookup keymatch lookup really only works on a column that has a unique list and if you think about it over in Excel lookup tables always have a unique list if you don't have a unique list when you're looking something up well then you get into trouble now notice supplier has duplicates so we're going to come over and we'll try and look up from supplier gel booms hopefully I spelled it right close with lookup close with positional and enter and the message is polite the key matched more than one row in the table if however I change this I'm going to ask for something that's not there the sunset Boomerang when I hit enter the message says Hey the key didn't match any rows in the table so just as over in the worksheet or in Dax when we're looking something up in a lookup table we want to do this kind of lookup on a column that has a unique list now those are some important points about row index and key match lookup but we got to learn about drill down with lookup and primary keys I want to come over and right click delete until end delete now if we look up into the formula bar excel.current workbook is a function that extracts all the objects from the current workbook now we can read this lookup for the row position it's looking through a column called name it's trying to find lookup example and then from the content column it looks like it extracted a table now let's remove this Delete and Enter and what excel.current workbook is doing is it's delivering a table with objects and the names for those objects now there are other M code functions that look into Data sources like an SQL database and delivers a table with objects and names so what we learn here about excel.current workbook will apply to many other Emco data source functions now what we want to look at is drill down if I want to extract this object right here I can click and get a preview I want to replace all of this with this table so I right click drill down there's the table if I get rid of this look at that it did key match lookup Source that's the table it looked through that name column found lookup example and delivered the table from the content column now when we did drill down the reason it did key match lookup is because back over here this table has a hidden primary key for data sources and functions like excel.current workbook and other sources like SQL databases when it delivers the table the names of these objects are a primary key which when you drill down causes drill down to do a key match lookup now let's compare that to this table most of the tables we get in power query do not have a primary key so what happens even though this is a unique list when I right click drill down that's not key match that's row index next lookup so even with something like drill down that doesn't seem like it's doing look up you got to know the difference between row index and key match lookup now the question is how do you know when a table has a primary key well it doesn't matter where you look in the user interface there's no button to click to ask whether this has a primary key now you're not going to have to do this often but I am going to show you I want to click f of x and there's a function called table dot keys and if we put a table inside when I hit enter it delivers a record and it doesn't even tell me which column is the primary key but it does say yes there's a primary key now I'm going to X this out right click drill down to do key match lookup now I want to show you how to add a primary key to a table we click f of x table dot add key open parentheses the first argument is a table comma the second argument is where you tell this function which column or columns you want to define the key now notice it says list so even though we're going to Define just one column as the primary key we still have to put it in list syntax hopefully I spelled it right there it is we'll Define that as the key comma and the third argument is true or false and you put true if you want a primary key enter doesn't look any different but when I right click drill down it does key match Lookup All right so that's a bunch about M code lookup and really the primary key thing is just so you understand what drill down does and we will see a bunch of important lookups including when we want to hard code and when we do not want to hard code in the next video now I'm going to load this as a connection only and now our next topic is custom functions now as we saw two videos ago in the Excel worksheet when we want to create a reusable custom function we use the Lambda function over here in M code this is how easy it is you start with parentheses and you list any variables that you want you can name those variables whatever you want then we use the go to operator it's an equal sign greater than symbol it looks like an arrow pointing from the variables over to the mapping of how we're going to use those variables so we Define X and Y and the mapping is X Plus y so whatever inputs we put for X and Y it'll simply add here's another example we want to calculate the effective rate based on the inputs APR annual percentage rate and periods number of periods per year so we Define in parentheses two variables APR and periods we put go to and that's the mapping that formula will calculate from APR and periods the effective annual rate now to learn the basics of building an M code custom function I want to build a custom function in five different locations the first is a substitute for a custom function and that's in functions that allow the each keyword we can build them as a new query that gives us a reusable function we can build them as a query step we can invoke that query step in a custom column and of course we can build them in functions that have an argument that require functions and the custom function we're going to build in all five places is based on our effective rate formula now here's our table and each record is an investment we have APR are in a number of periods and in a new column I want the effective annual rate and of course from Finance the effective annual rate is always going to be bigger than APR if the number of periods per year is greater than one now if we have a simple formula like we do here and you only want to do it one time there's no need to build a custom function because we can go to add column custom column and this feature will use the table dot add column function and then the third argument where a function is required it uses the each keyword and each is a substitute for a custom function and much easier to use let's name this effective rate each and our formula is going to require an exponent but an M code unlike the worksheet in Dax we don't have access to that carrot so we have to use the function number dot power right so the base for our calculation is 1 plus APR divided by periods per year that's the base of the number comma the power that's the exponent double click periods of year close parentheses now something very important we just double click this field name and put this in field access operators field name because we're using each that's going to grab the value in each row as this formula is copied down now when we click OK come up to the formula bar at the end I'm going to type a comma and I want to Define type number that'll be decimal enter with the icon 1.2 and I see here that I did not subtract one this is one plus the effective interest rate no problem we come up to the formula bar and right here minus one and enter and now we have the correct annual effective rate and sure enough the effective rate because all of the number of periods per year are greater than one all of these are greater than the original APR now in this example we're just going to calculate this one time so that's the way to go but now if we're going to use the same calculation over and over in this workbook we want to Define our own reusable custom function and that means we create a new query right click new query other sources and there it is way at the bottom blank query we don't type it up here at least I'm not going to I'm going to go to home Advanced editor I'm going to highlight all of this I'm going to call this effective rate and that's the identifier we're going to use and because this is a let statement we type an equal sign and now we Define the function this is where we put open parentheses and the nice thing here is I do want to name these because these will show up in the dialog box when we invoke this function so I'm going to give them names that make sense I might even call this periods per year if I was expecting someone to use it that might not know the difference between year and number of periods per year but I'm going to use those go to number dot power 1 plus APR divided by periods comma this is where I put the power and I can see it right there I can actually hit Tab and I'm always so afraid of intellisense and M code minus one double click because I want the query to return the actual function there's one step that's the identifier equal there's our function this is what the query is going to return click done I'm going to rename this over here effective rate and enter we have it selected so we see this but now I want to come over to future value table add column we click invoke custom function the new column name I'm going to call it effective rate Universal function and from the drop down all the different functions in this workbook will appear we have effective rate and we get to select now sometimes it's on any you switch it to column if you want to column it already guessed right here we want to say column Name by the way this means you can put whatever you want type something there but we want a column and we want periods per year now I click OK and just like that I have the same result now there's a couple other things we can do to a custom function right click Advanced editor we can actually Define data types I'm going to Define this as a number that's for that first variable as number two variables each have a data type and then directly after the open parentheses but before the go to I'm going to give the output from the function a data type also I click done now the output from this is number but of course that's the value that's being delivered we also want to add a data type which of course we put comma in the end of this function remember data types and values are different things click at the end and enter and we want to go back again right click Advanced editor because there's a third cool thing we can do when we're building custom functions we can add notes to a function actually these notes can be added to any M code when you do forward slash forward slash that's for a single line even though there's two lines here I didn't hit enter until I got there if you want multiple lines that means I hit enter enter Then to open forward slash asterisks and to close asterisks forward slash so we have our function with our go to operator we've defined some data types and we even added notes click done future value table and if we scroll over we have used each because we were doing this calculation just one time and then we built a custom function and invoked it up an add column because we were going to use it over and over in this workbook the third option is to build your custom function inside the query itself as a query step when you do that the function's only available inside this query now you might want to do that because you don't want a bunch of extra queries and a function query over here now before we go to Advanced editor and build our custom function I definitely want to rename these steps once I've done that right click Advanced editor all right the first thing we want to do is the last line well that's what the query is delivering but this is no longer going to be the last line so I type a comma enter I want to add a note so forward slash forward slash now there's no spell check in advanced editor hopefully I spelled everything right and this is where we're going to build a query step with an identifier that's the actual function ER query FX that's identifier equal sign and this is where we build our function now I'm going to use variable names that don't need to be so descriptive because this function is just going to be working here as number so I will be for APR comma n will be for number of periods those are the two variables and for the function output will Define it as a decimal also go to the base will be 1 plus I divided by n comma n is the exponent close parentheses minus one we want to type a comma because we're not going to let that be the last step we're actually going to use it now here's the hard part meaning I'm going to type out adding an extra column to the ER Universal step the new query step name will be ER invoke f x equal table dot add column there it is open parentheses and because this is going to work on not the function step but the Step Above we're going to copy that that's a Full Table this function will add a column to this table comma the field name for the new column ER query FX comma and this is where we're going to invoke our function I'm using the each keyword and the function we're going to use is just the formula that comes after each ER query I see it right there tab open parentheses and that's kind of cool up here in advanced editor we get a screen tip however if we try to invoke this in a custom column outside Advanced editor we will not see this the downside here is that we have to remember what the field names are and type them in field access operators APR comma field access operator periods per year and then I definitely want the fourth argument type number flows on table.com I don't want a comma because I want to return this double click copy the identifier and after in control V click done and there we did it a much harder way but if you don't want an extra function out here and you're going to use it multiple times within this query that's definitely an option now if you really did have a function inside this query and you're going to use it multiple times the other way to do this is use custom column we cannot use that because it's not a query it's inside but we can try and invoke it from Custom column we type the name of our custom function open parentheses and the advantage of doing it here is now I can just double click comma get both Fields close parentheses and now when I click OK it looks like capitalization matters so capital e r enter and if we scroll over that will work too comma type number click at the end and enter all right so if it's a one-time deal we're definitely going to use each and just type out the formula Universal if we want a reusable function that's the way to go by the way this step right here has that little information there's the note I added if we click the function we can see that over here here's the function where we actually typed it in the advanced editor and if I rename this that's where we use the function specific to this query but built the formula in a custom column now I want to go up to ER each I'm going to copy this entire step control C Escape come to the last step click f of x highlight everything in control V when I hit enter now we want to talk about the each keyword and look at how it is a substitute for a custom function now the first thing about the each key keyword is it shows up in lots of M code functions like table and list functions where a function is required the second thing is is if we don't have it then this formula is not going to be able to calculate in each row in fact if we don't have it we get an error if we put it back in we don't get an error now to understand each we have to think about Microsoft's definition and their definition is a syntactical shorthand for defining an unnamed function take in a single untype variable named underscore and so directly this is what each is replacing go to open parentheses to Define a variable there's the underscore there's the go to and when I hit enter it of course works perfectly and that's what each is replacing and that underscore should sort of make sense because earlier in this video we used each and an underscore to extract the record from each row in a table so we're lucky that we don't have to type that out every time we want a formula to calculate in each row of a table we can simply use each now how is it a replacement for a custom function well the third argument requires a function open parentheses and you can call the variable anything you want that's going to represent every Row in the table then we go to and the crazy thing is for every field that we want to access in each row we have to put that named variable directly in front of the field axis operator and so now when I hit enter that's how each replaces a handmade custom function so what this means is for a simple formula like this we are glad that there's the keyword each now in just a few moments we'll see a formula that's going to calculate down every single row and we can't use each and so in that case we will have to Define a variable and put that variable in front of each one of the field axis operators now I went ahead and added three steps to leave a trail here there's the each that's directly what each is replacing and here's what each is replacing if we had to type it out by hand now our next example of a custom function is we want to go over to the disk discount table and look at how to do approximate match lookup now here's a lookup table and over an F sales for each sales amount we need to find an approximate match lookup and return the discount now of course in the Excel worksheet this is simple we just use x lookup but in Dax and in M code there is no approximate match lookup now we're going to look at two ways to do this and the first way we're going to look at works both in M code and Dax and if you have my the only app that matters book in fact when I teach you the M code for this I I immediately teach you the Dax also but here's how the formula logic works if we have a lookup value of 750 bucks I'm going to ask the entire column how many of you are less than or equal to 750 bucks we'll get true and true so the table will be filtered to show just the first two records and then we'll extract the discount column because this is a table we just use field access operators with the discount name and it extracts it as a list and then we'll use the function list dot last it'll get the last one now we're going to start off by going over to F sales up to add column custom column I'm going to call it discount custom function one and for the time being because I'm allowed to go and get any other query all I'm going to do is reference this discount table I I see it there this will return a table for every row click OK if we look at the table sure enough we have the lookup table in every row now there's a couple problems with this one is if we leave it like this because this is an external query every time the formula needs the table it has to go and get that table and if you have a lot of records that takes a lot of time so I want to go over to this discount and actually the second problem is I want to make sure this First Column is sorted because if we're going to go and get the last value here this better be sorted so we're going to add a step sort ascending I renamed it and now we want to go up to f of x and there's the final table that's this right here and we want to buffer the table which means load it into memory once then when we need the values we use the values from memory instead of having to go back to the original query we can use table dot buffer open parentheses close parentheses and enter all right I renamed both of those let's come over to F sales and we want to come over and edit this formula remember all we have is a table there and that table is coming down every single Row in the table dot add columns function but we want to open the dialog box and what we need to do is filter the disk discount table so of course we're going to use a great function table dot select rows open parentheses it needs a table and then in the second argument it needs a function so I'm going to type comma and then of course we want to try and use each because it's easier but we're in big trouble already this is a function and we're going to put a logical test here that filters the sales column in this discount remember less than or equal to that 750 well that sales column is coming from here this whole formula is iterating down every Row in this table inside of table dot add columns the two each's are going to be in Conflict not only that but what do I need to do inside of this discount I'm asking the question of a sales column and as the formula goes down each row I need to also ask the question of this sales column so there's no way that we can use these two each's this is a situation where we need to define a custom function I'm going to open parentheses and call this inside table that means wherever I use it it's going to get the sales column here and because I'm going to leave each out here I can just reference sales with the field access operator so it go to and watch this I'm going to cheat what I really want is is this but it doesn't know how to differentiate between those two sales so I put it in front of the first sales now the formula knows how to look in this discount through the sales column and compare every single row to the sales in every single Row from the outside table close parentheses click OK and you got to be kidding me look at that it totally worked there's that 774 both of these get true because they're less than or equal to that 774 and now we just have to look up that last value now I'm going to edit the formula up in the formula bar and we can see our table to extract a column as a list I need to use field access operators with the name discount so all of this right here even though it's a function it's actually a table so right after the closing parentheses for table dot select square brackets discount hopefully I don't spell it wrong and enter and now we have exactly what we want we have a list now we can simply use list dot last after the each space list dot last open parentheses and notice it wants a list and then after discount Out close parentheses and when I hit enter bam there's approximate match lookup and this is a good example of when in a custom column in an argument that requires a function we have to know how to create custom functions now I want to show you some alternatives for M code custom functions when we're doing approximate match lookup I created some extra steps but I went ahead and let's go up to Advanced editor and I added some formatting to make it easier to understand and compare here's the one that we just created and because we're using two different back to back functions table dot add column and table dot select rows that both use each and iterate over a certain number of rows we have a conflict if we put each in the second function so what we did is we kept each for the first function and then defined a custom function with a variable then when we got to our formula where we had two columns coming from two different tables the it defines sales from this discount and sales came from this table and table dot add columns a slightly different approach instead of using each in table dot add column I actually defined a custom function for the first function and the second function the first variable I called OT for outside table the second one I T for inside table then it's easy to distinguish between the two sales column it comes from this table OT comes from this table up here still another way is to use the let expression remember let just allows us to Define variables So within the scope of table dot add column I said here's a a new variable called sales we're going to let it equal that field and because it's in this Scope when we use the variable sales down here it's automatically going to iterate and get each value from this table that allows us to use the each keyword when we get to the second function so we have sales in field access operators that's coming from table dot select rows and this variable that we declared using let that comes from table dot add column this table right here now I'm going to click cancel there's another method and this is from rpal gur if we click on this separate query go up to Advanced editor he decided to use list dot accumulate and that's like the scan function we learned in Mex video number 10 when we talked all about Lambda and this formula right here we'll calculate a little bit faster than the other three formulas we just talked about and in the download zip folder there's an Excel file called approximate match timing and the timing and rankings are there for various approximate match lookup formulas wow that was an epic M code video and in this video we talked about custom functions both building them as part of a larger query or building a separate reusable function we talked about each and underscore and how that is a substitute for a custom function we talked all about M code lookup including row index lookup and key match lookup and we talked about the related topics primary key and drill down we talked about the heart of M code which is understanding all the different values and how they work we talked about different data types we talked all about the let expression and the Syntax for a lead expression understanding that by default anytime we create a query a let expression is used but more importantly the let expression is just like the let function in the worksheet it just allows us to Define variables and create some output we talked about the three places that we can view edit and create M code in the applied steps pane the formula bar and of course Advanced editor and of course we talked all about how the Amazing Power query and the M code language that builds everything in power query is both in Excel and in power bi desktop all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from Excel is fun more mechs video and this video is a full free course on M code so tell all your friends post the link to this everywhere so this free lesson about M code gets out to all the excellers and power bi users in the world foreign [Music]

Channel: ExcelIsFun

Views: 54,464

Keywords: Highline College, Busn 218, Mike Girvin, excelisfun, Mike excelisfun Girvin, Microsoft 365 Excel, MECS 12, Microsoft 365 Excel Complete Story, M Code, Power Query M Code, Power BI M Code, Data Mashup, Learn M Code, Free M Code Class, Learn M Code Fast, M Code Made Easy, M Code in one hour, M Code Language, Easy Power Query Values, Custom Functions, M Code Custom Functions, Power Query Lookup, M Code Lookup, Approximate Match Lookup Power Query, M Code Specifications

Id: 3ZkIwKBVkVE

Length: 101min 38sec (6098 seconds)

Published: Fri Feb 03 2023

Reddit Comments

[u/michaeltyler]

tbh, the audience here doesnt have much interest in this high level excel feature-set. you're much better off posting this at /r/excel !

[u/oneofthemanymillions]

This is an awesome and comprehensive video, but honestly would be better if it was 10 separate clips. This channel is my go-to for excel excellence, there is none better far as i know.