The Magic of Working with Lists in Power Query

if you are starting out your journey of learning the M language in power query the very first thing that I will recommend you to learn is working with lists in power query what lists allow you to do is create more Dynamic queries that will not break in case anything changes with your data in this video I'd like to talk about two very interesting examples of working with lists and how could they possibly create more Dynamic queries this video is all about working out the magic of lists in power query you and I let's go take a look at some magic all right people the first thing that I would like to talk about are the bare fundamentals of what exactly is a list a list is nothing but a single one columnar data it could possibly contain numbers it could contain some values it could contain multiple number of rows but the columns are just going to be one now what defines or what separates a table from a list list is always one columnar probably a table could also be a one columnar table but that does not make the table into a list what makes the list as a separate object is the curly braces in which the list needs to be defined always so whenever you have the opportunity while you're taking a look at the M code in the formula bar please do take a look at the curly braces in which the lists are going to be defined so lists are always going to have these curly braces at the start and at the end within that a list is going to be defined in this video I'll talk about opportunities to identify uh where all probably do you get a list we'll also take a look at how do we extract a list manipulate that list and maybe make it more Dynamic and plug that back into the query so that the query starts to become more Dynamic all right let's just go with some data and start playing with lists okay as the first example here I'm working with some random data take a look some stupid data here we have name column one column two column three and a couple of junk other columns that we have now the condition that I'm trying to build here is that I would like to be able to capture the name column and all the other columns that contain the word column within that so there are three columns like column one column two column three along with the name column is what I'd like to keep and remove all the other columns that I have sure enough you can do that with the user interface as well so what you can do is select the four columns at the start right click and you can say something like remove other columns but what that does is that actually hard codes the column names within the formula bar so you can see that column one column two and column three they have been hard coded now tomorrow column 4 gets added this is not going to be captured within my query and the query obviously breaks now what do I do about that if you also carefully take a look at the query that we have been able to make using the user interface at the moment is that we have been able to uh make a list so take a look that that all of the names of the columns they are within the curly braces and this is nothing but a list like a one columnar data this is the first row of data the second row of data the third row of data and the fourth row of the data what I'm going to do now is that I'm going to be able to extract all the names of the columns manipulate that and plug that right here so that the column names become Dynamic tomorrow if column 4 gets added that gets updated in my query automatically let's just start with the first step of extracting the column headers in the form of a list that's our first step so what I'm trying to do here is that I'm trying to go ahead in this table and in this table I'm trying to get all the names of the columns right so what do I do I create a new step and I say that from the source table which is nothing but the entire table I would only want to pull up the column header so I'm going to use the function called table dot column names and as soon as I do that and I close the bracket and press enter this is going to give me a list of all the names of The Columns of the table note that we have been able to get what we have been able to get a list now what happens in power query is that when you're working with the list the list has very limited features of the UI you can't really apply filters you can't really do a lot of things with the list unless that list is converted back into a table if you go ahead and take a look at all of these options right here all of them have been grayed out all of these options have been grayed out because lists have very limited UI features in power query now to be a able to work with this list using the user interface I need to convert it back into a table don't worry we'll convert it back into our list later so I'm just going to right click on that list and I'm going to say that I would want to convert it back into a table because I would want to do some operations on top of you so I'm going to say that the delimiter is nothing in the if there are any extra columns please truncate that and click on OK and this converts back into a table now what condition do I want to write here I would like to pick up the name column I would like to pick up all the columns that start with the word column sure enough I can apply a filter don't go here and say that text filters and I'm just going to say contains and I'm going to say hey it should be equal to name that's my first condition or it could probably begin with something like a column right those are the two conditions that I'd like to go with click on OK and I am left with all the names which are valid as of now that I would like to keep problem is that this is as of now a table and you can see that here here the output of this is a table and this the output is a table as of now but I would like to have a list and the icon is going to change to a list right here now how do I convert it back into a list because if you remember what we saw in the table dot remove columns functions we were required to input the data in the form of a list and unless we do that it's not going to work so I'm going to right click here and I'm going to say drill down this is going to convert this data back into a list all right pretty good now I'm going to create a new step and in the new Step I'm not going to refer to column one but I'm going to refer to the entire table which is nothing but the source table which contained all the other columns so I'm going to go to go to this and I'm going to call out the source once again I get the source and this time I'm going to again do that manual work so select the name column the column one column two and column three right click and I'll say remove other columns where are you remove other columns and that is good to go now the problem is that the name column one column two column three are again hard coded but now nothing to worry to be able to automate this part we have been able to create a list and you can see that this is our list that we just created which is nothing but column one let's just give it a better name so I'm just going to call this as column list not column lost but column list and press enter now this list is going to go to this particular step so I'm gonna get rid of all of this and call out my column list now this is good enough the formula was asking you for a list you have provided a list that should just work still gives you the same output but there are no hard codings in the formula that means tomorrow if another column gets added your query will not break let's just graduate from working with simple list to slightly more tricky examples of working with lists to make your query Dynamic take a look at this data we have been working with this data but this data has got two errors two error columns so while we were importing the data from Excel we had a couple of any errors that cropped up and I won't want these errors to be gone so what do I do I go ahead and pick up these two columns column two and column three I go over to the transform Tab and I say that I would want to replace the errors with a null value so I say null and we say okay and the errors are sure enough they are gone now the problem is that again the names of the columns have been hard coded in the formula and I do not really want that what if another column may be column 1 or column 4 then starts to show up the error this formula is not going to handle that very well so if you now take a look at the formula I'm going to see that I still have a list structure but slightly differently structure let's just evaluate that so if you take a look at this it says the column 2 is going to be replaced by null and the first pair is going to be packed into one list curly braces at the start and curly braces in the end then it has another list which is the other column which is column number three name of the column and you'd like to replace that with the null again into a curly braces now we have two pairs as of now so column name and the replacement value column name and replacement value these two pairs have been again packed into a form of a list so we have like a nested structure so we have like a list a list here and within that list we have two sub-lists there is going to be a list right here and there is going to be a list right here as well sorry for the bad handwriting that's what it's going to have so we need to have like a nested structure that we need to supply it to the formula in order for the formula to work a list and within that again a list how do we create such a structure so what I'm going to do is I'm going to jump back a couple of steps and take a look at the work that I have done and try to build this nest structure remember list of a list is what we're trying to get we're going to start to evaluate my query and take a look at the previous steps so if I just go back to the source right here this was all good then I took out the column names as headers right here and I got all the column names to which I then converted into a table so that I can work with that I applied filters and now I'm left with only the columns that I would like to work with now all of these possible columns that I'm trying to work with could probably have errors and I'd like to remove the errors from all of them so what do I do I want the name column and I would want to write the null value right here to be able to get you know replaced by the nulls and similarly I'd like to write null null null everywhere else so I just go ahead and I create a column and I say that in that particular column I'm going to write a value called null and I say okay and that's the null so this is the name of the column and this is the null value that it gets replaced with now pretty good the problem however is that a list could probably contain just one column not two columns as of now this is a table and the table contains two columns the name of the column and the replacer value the name of the column and the replacer value what I need to do is I need to convert this entire thing into a single list that means this thing becomes one list this thing becomes another list this thing becomes another list this thing becomes another list so on and so forth how do we do that let's just take a look so I just go over to the next step and the First Column from here gets converted into a list we are using this list sometime later in our query so I'm not going to disrupt this step I'm going to create a new step right here click on OK and I'm going to refer back to the added custom step which is where we have pairs of two the name of the column and the replacer value so I'm just going to say that this is equals to added custom and press enter and we get with that now the first thing that I need to do is these cannot be rows of the data they have to be The Columns of the data so I will transpose the table so I'm just going to come right here and I'm going to say something like table dot transpose now you can use the user interface as well but I like writing the M code just keeps me in practice so I'm just gonna say okay for now and this transposes the table now as of now you can see that a list is going to have just one column of the data so this becomes like one list this becomes like second list this becomes like a third list and this becomes like a fourth list so all the four columns need to be converted into individual lists how do we do that there happens to be a function which converts all the columns that you have of the table into their own individual list so what do we do we have a function called table dot two columns and I can just wrap this around this and close the bracket and press enter and now all the four columns that I saw have been converted into a list so name column which is the name of the column gets replaced with the replaceable value null and the second column also gets replaced with the value null so on and so forth now the structure that we were trying to chase was a list of a list structure have you been able to get that sure enough so take a look this is an outside list again one column this is an inside list which is contains the two pieces of info mission that we are seeking name the column replace for Value pretty good let's just call this as replacer or rename column something like that rename columns and press enter now I'm going to go back to my step right here which obviously is giving me an error let's just go fix the error so in custom two uh this is good um here this should not really refer back to the names of the columns because this is a list of a list it should rather refer back to the column list that we originally created so I'm just going to correct this so column list this is good to go the bracket was missing and this is good to go and replace errors is working now as of now the problem is that the names of the columns have been manually inputted right here it is a list of a list structure and I'm just gonna get rid of all of this and replace that with rename column step that I have created all right pretty good press enter nothing changes all is good but in case any errors crop up here or crop up here or in the new columns this is going to just stay all good all right that's been it let me know how did you find this one I wanted to make this video to drive three key learnings that are going to be essential number one is that you should definitely take a look at opportunities where you can find a list in the formula anything that has curly braces or double curly braces that denotes a list of lists those are the places that you can probably carve out of the formula and plug in your custom lists now to be able to plug in the custom list the second thing that you need to understand which is very important is that how do you extract something in a form of list this could be names of the columns this could be a record or anything else that you would want to be able to extract in the form of a list and the third which is a very very important one is that if the formula is asking you for a list don't try to input the table over there the formula obviously is not going to work so feed the list if the formula is asking you for a list let me know if you found this one helpful and in case you have any questions around this I'll be glad to help in the end up Big Show out about my attacks and my power query courses in case you are a beginner and you'd like to master the fundamentals of power bi which is Dax data modeling and cleaning and shaping up of your data in power query I'd highly recommend that you take a look at my courses to get on top of simple and more complicated problems and you shall benefit a lot thanks so much for watching this and I will catch you guys in the next one cheers bye [Music] foreign [Music]

Channel: Goodly

Views: 73,214

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

Id: 90atXaUhBec

Length: 14min 27sec (867 seconds)

Published: Fri Oct 14 2022