Table Navigation Tricks in Power Query

in today's video i'm going to talk about two very interesting navigation tricks in power query of course using the m language no further ado let's start all right trick number one navigating using lookup operators now that obviously is a jargon let me help you understand what am i trying to do here in my current excel file i have a three tables these are the three tables that i have and these tables have also been named so this is the first table the second table and the name of the third table now i would like to open up a table and by opening up a table i mean to say that i would like to navigate to a table now there are a couple of ways in which you can navigate to a table or open up a table the first thing that i'm going to do is i'm going to go to row number two here actually this is not row number two this is row number one because in power query the counting starts with zero so this is uh row number zero this is row number one and this is row number two so i will first want to go to row number two and then i would want to pick up the column which is the content column if i provide these two coordinates then i will open up this particular table which is called blank letters let's just take a look so after i have the entire table this is my entire table in this table i need two coordinates one is the row number which is not this row number this is row number one and the second is the name of the column if i provide these coordinates i am going to navigate to this particular table let's just see so i'm just gonna go right here i'm just gonna say hey my row number is row number one if i now press enter i am only going to get the contents of row number one you can see that this is the table now obviously i would want to navigate to a column as well which is the column the name of the column is the content column so i'm just going to write the name of the column in the square brackets all right as soon as i commit to this now the table number one which was blank letters has been opened up in front of me and i can start working in this table this is one way of navigating to a table the second way of navigating to a table could be using a lookup operator what do i mean by that consider this for an example now let's just say that row number one could keep on changing and you may not have the exact table at row number one so what do i do about that i'm going to actually use a lookup operator to navigate to a table now that i know that the name of this particular table is blank letters i'm going to do something like a lookup in this particular column so i'm just going to say hey why don't you go in every single row of the name column just go find something like blank letters once you find that open the corresponding table of that particular column how do you do that lookup operator kind of thing now the first thing that i'm going to do is i'm just going to create a curly brackets because in the curly brackets you mentioned what row are you trying to navigate to the problem however is that i don't really want to write the row number i want to do a lookup operator based on the name column to write the name of the column i'm just going to put the square bracket and create this as a record so i'm just going to say hey in the name column i'm trying to navigate to something like a blank letter right make sure the case sensitivity is there and once you do that and if you now commit to this you're again going to get to this particular record right here which gives you both the columns and now you're trying to refer to the content column right here which again comes in the square bracket and you have now opened up the entire table now i'm sure uh if you have worked enough with power query this is pretty standard thing in power query now if i let me just cancel this out even if you happen to click on the table the lookup operator happens to work automatically if i click right here you can see that as the second part which is nothing but the navigation part it took the name of the table which is the entire first step right here and then it automatically added the lookup operator which is the name column and the blank letters and the content after that now i purposefully wanted to talk about it because you can use this particular navigation trick in some other aspects of your work as well to make your queries a bit more sophisticated now there is only one thing to remember that once you actually navigate to this particular record right here which is the name column make sure that there is only one row which matches this particular description not many rows if you have many rows right here which match the name description this is going to give you an error all right let's just move on to trick number two okay navigation trick number two is a question mark no i mean to say that i know the trick it's not a question mark but the trick itself is a question mark let's just take a look so here in this particular table we have two columns the content and the name the same columns that we have been working with and i will try to set up some navigation and i will try this time to purposefully create some errors and let's just use the question mark to deal with those errors please take a look now i know for sure that in this particular table there are just two rows of data actually three rows of data but the counting starts with zero so this is the zeroth row the first row and the second row what happens if i happen to write number three right here let's just take a look if i happen to write number three right here and i commit to this this is obviously going to give you an error it says that there weren't enough elements in the enumeration this is trying to say that i was not able to find the third row of the data now what do you do if you just happen to write the question mark at the end it's going to give you a null value and the query is not going to break now this can also be replicated through the columns as well now let's just say that i correct the query and i just navigate to row number two i press enter i get the correct table now i'm going to write the wrong name of the column now there are two columns right here there is a name column and the content column and i'm just going to purposely make a spelling error i'm just going to write the name of the column which is nothing but contents a plural and if i now commit to this and i press enter obviously this is again going to give me an error how do i solve the error i just happen to write a question mark in the end the question mark actually means that if you're not able to navigate or if the navigation is not really found then just give me a null value this is awesome and would not let your queries break now one more thing before i go now what you can do is you can also put question marks at both the places at the row number and as well as add the column number now i can do something like three here uh and if i now kind of put a question mark here this is going to take care of the row in case the row gives an error and if i put a question mark here this is again going to take care of the column if the column gives you an error this is also going to work if you happen to use a lookup operator right here so if i cancel this out and if i probably write something like name equals to sales so if i just go right here and i say that the name equals to sale i'm purposefully writing the word sale here although the name of my table is sales and obviously this should actually give me an error now if i actually commit to this this is again going to give me an error because the name sale was not found there was sales but not sale now what do i do i just write a question mark here and this again gives me a null value pretty awesome all right that's been it let me know what you think about this and if you have any questions around this feel free to drop in a comment and i'm going to be glad to reply in the end a big shout about my tax and my power query courses in case you're starting out your journey with power bi and you don't want to build your fundamentals first in power query and index and in data modeling i highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking around and i'll catch you guys in the next one cheers [Music] you

Channel: Goodly

Views: 14,820

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

Id: XH3hWI6stIQ

Length: 7min 16sec (436 seconds)

Published: Fri Aug 05 2022