Power Query Masterclass on Lists with Oz du Soleil @OzduSoleilDATA

I declared a list within the CI brackets every single item is separated by a comma I can have as many as i' like and that's nothing but a list and we have literally replaced this list which was a hardcoded list with the dynamic list that we created okay I gave Oz a proposition and I said oh hey why don't you just give me like 30 minutes of your time and I'm going to help you understand the power of M language especially lists like that was a thing I'm aware of M and I've done some small modifications with M but it's really list that I'm curious about all right everybody's welcome oh such a pleasure to have you how are you doing today I'm I'm all right I'm all right good to good to see you again good to be doing this with you uh yeah um and it's been a while since London where we had a good time but yeah I'm I'm glad to be here absolutely what time is it of the day at your place it is 907 p.m. do you sleep by that time or I'm kind of keeping you up oh man no I go to bed about 2 a.m. so I'm okay night tow is it yes exactly exactly and that's why um that's one good thing about the move to Las Vegas is it suits my night our lifestyle ah I see I see what would one find you doing let's say at 1:00 in the tonight well there is a cigar bar that's on the Strip that I like and there's some really cool people uh some regulars that I like hanging out with and then I always meet fascinating people who are in town for conferences or something so that's one thing and they don't close until 2 so at 1:00 yeah I could be there uh I could be out just driving around looking exploring the city I like to do that uh just to get out of the house I love driving man I was gone for about 3 weeks uh for the MVP Summit but most of it was me driving and exploring uh or Twilight Zone I like that show and that comes on at 12:30 and goes off at 1 so you know so there there there are things oh and there is a really nice 24hour martini bar about 2 miles from where live I see and yeah but it's it's a good place um cuz usually in other cities places that are open at 2:00 in the morning they're kind of run down and trouble but this place is really nice and really nice food really nice cocktails excellent excellent yeah uh we just got our conversation started but uh I definitely would want you to introduce yourself as to uh who you are what is the work that you do you obviously have a big label of XL MVP for the very few people out there who might not know you and that's a shame but you want to you want to maybe say two lines about yourself as to who you are and what you do um I Dole I'm from Chicago but I live in Las Vegas and I have the YouTube channel Excel on fire I haven't posted in a while but I I'm not done I haven't quit and I am a LinkedIn learning instructor I've got a bunch of courses on that platform and every Friday an Excel challenge comes out and then every other Monday uh uh our power users challenge comes out and they're all real world and they introduce you to new features of excel a lot of people like them uh what else what else uh oh I collect kaleidoscopes this is my newest Kaleidoscope damn from uh mikio out of Japan yeah that arrived um I got that couple weeks ago so I got to know about kaleidoscopes when I saw your videos and you mentioned them on the videos um until that before I've never heard of that word so if there are any people out there what does a kaleidoscope do like what what is it about a kaleidoscope is some type of an object that has a mirror system inside and then an object Sail on the end and a lot of times they there could be plastic there could be uh glass inside I've got one Kaleidoscope that has feathers inside and so you look through it and you can either turn the end and some you have to turn the whole thing and it just makes beautiful images awesome awesome just to give everybody a backstory yes I have never met Oz I mean I've you only seen you um like a fanboy on the internets on YouTube back in the day when I started working with Excel and I still remember that you were coming uh to the London Excel Summit uh this time and uh I saw your name and I was very excited to come of meet you and it was night when I arrived at the hotel and as soon as I got inside the hotel you were standing right there uh I believe you were the first one to recognize me and said you said hey Chand welcome and you know and the greetings and stuff like that and we hugged and I was thinking oh my God I just hugged o d that was awesome wow man you know and and I feel the same way I you know cuz I've been a Chand deep fan so this is this is interesting so yeah to to see you on different um different meetups and stuff and to see your work and say my gosh this guy is smart you know but but you know but the thing is you can be really quiet though it's like what this gy guy you know and so so it was I was looking forward to meeting you to to find out you know who you are to meet you and ab yeah and it was a great time in London absolutely absolutely while parting ways in London Oz and I were speaking in the hotel lobby and we just got to speak about powerquery for the first time in the entire Excel Summit and all said that hey I love using power query but I'm mostly the user interface guy I've never really worked with the m language before for uh although I can make things work but then I don't understand the utility of lists and you know all the you know things behind the M language so I gave Oz a proposition and I said Oz hey why don't you just give me like 30 minutes of your time and I'm going to help you understand the power of M language especially lists like that was the thing that's right yeah yeah because one thing one thing that I will I will say is that I have written a lot of code in the past I don't like writing code so I'm aware of M and I've done some small modifications with M but it's really lists that I'm curious about sure um because I've followed instructions online and I've gotten things to work and part of the instructions say you got to do a list and then you got to do all this other stuff okay so I'm following the instructions I don't care so much about the M it's like why did I have to do a list what does a list do you know when are they necessary and you know those kind of they're a mystery to me sure sure and then um I got back home and I was thinking about uh doing a video and then I thought hey I'm going to do that with Oz but it's going to be interesting to record all of that in a video and just maybe post it out for anybody who's interested to watch so that'll be nice to have yeah cool so here we are absolutely absolutely shall we shall we start awesome so I'm gonna share like James Brown would say I'm gonna I'm gonna share my screen let me know if you can see it I can see it all right let's start with the blank query so I'm going to go over to the data get data from other sources and a blank query and I'll pull up that window right here now the first things first was is that if I have to talk about a list list is just another type of a value or think of that as an object in power query so you have like a table you have a number you have a text similarly you have something like a list okay now now what I'm going to do is I'm going to go up in the formula bar and start to create a list now the list creation is going to be always within a cly bracket so I'm going to start a curly bracket and end a curly bracket and within which I can create a list and list can technically have anything within it so let's just say that I write five numbers 1 2 3 4 and five and that is a list now notice that you're going to see that all the numbers are separated by a comma so all the list items are going to be separated by a comma so got five items in here five numbers 1 2 3 4 5 and that is nothing but my list I declared a list within the CI brackets every single item is separated by a comma I can have as many as I'd like and that's nothing but a list once I commit to it um I have a space at the start once I commit to it you're going to see that I have a list that's what a list looks like you're also going to see that the query that I just created doesn't really look like the typical query that you have in power query you would have a table icon instead here but here you have a list icon it tells you that the query that you've created is nothing but a list good to go so far yeah as of now I fed the numbers in a list well I could also feed in a text in a list and obviously you would understand that any text is going to be in the inverted commas and now one of the items is a text number that is the simplest way of creating a list yeah you start the curly brackets you feed in the items one by one by one except each of each one of them separated by a comma you can have text you can have numbers I'm going to also show you more complicated examples of a list but for now this is nothing but a list I'll follow all right cool sometimes not sometimes but actually most of the times you're not going to be creating lists manually you're going to be using your data manipulation or your tables or your data sets the dirty data sets to be able to do something in that data and that requires a list so I'm going to go ahead and start to do something so I'm going to go in the Home tab and I will enter some data to create a table of it and let's just enter some data so I'm just going to say that the First Column is let's say a letter and the second column is nothing but a value so I'm just going to say value and I will just maybe write three letters a b and c and then against that I will write one two and three three values of it I'm going to call this as let's say some data click on okay and that is my table created so that's my table change type step has been applied that's okay the next thing that I'd like to talk about is is that at the moment if you have a table from this table can we extract something as a list so we have two columns we have the letter column and we have the value column at the moment it's it's a table so if you take a look at the icon here it's a table it's not a list and the icon also shows right here but can we extract a list from this table the simple syntax to doing that is nothing but you write the table reference table reference and then you will write the column reference this is the syntax so you write the name of the table or the reference of the table first in the square brackets you're going to reference the column name and then what you get is a list so for example change type is nothing but my table which is right here you can see that and if I were to mention change type and square bracket if I were to mention the name of the column could be the value column or the letter column I would be able to extract that column in the form of a list at the moment this is just Theory nothing valuable is coming out of it but we will get to that in just a bit so if I were to just go go ahead and create another step right here you can see that it refers back to the previous step automatically and that's a change type step you've done that a lot of times in the past and here I will initiate the square bracket in the square bracket I can put in the value column or the letter column so let's just say value and reference of the table reference of the column and I press enter and what I get is a list which is nothing but the same three values 1 2 3 but now in The Next Step they are converted in the form of a list and that's a list and it's different from if you had just deleted that other column and then you'd wind up you'd still have a query but You' have a query with one column that's right that's right okay the thing about list is that a list is never going to have a header it's always going to be labeled as a list you'll just have like list and then the values of the list and the other thing about the list is that the list cannot be multic columnar that means if you were to ask me hey could you probably get both the columns as a list I cannot the list is only going to Happ have one column not more than one column right no headers just one column as many values as you would want that's a list so far what we have done I'm taking notes here okay oh thank you okay so for now what we have learned is how do you make a list in case you would want to make one and the Syntax for that is the curly braces and you can have as many items as you would want separated by a comma simple enough and then I've also seen where you can do like a one dot dot that's right 700 and then okay so if I were to just go ahead and make another step so FX right here and I can say something like hey I want to I want to make another list so I can say one dot dot 10 so the dot dot is like the expanding feature of the list creation feature it says Hey start from here and go up till here and what you would get is nothing but a list of 10 numbers you don't have to type out every single number well funny enough you can also do that with alphabet so if I were to just go ahead and say a do doj and you you're going to get the letters as well so I don't think that you can't you you can do Monday dot dot Sunday or something like that because that's not recognized by the unique character of the keyboard characters but anything which has fed into the keyboard and is in a chronological order like 1 2 3 4 5 6 7 8 9 10 the alphabet starts with 65 as a character go up to 96 or 97 all of those things can be generated through a list cool so far so good yeah now I'm just going to maybe duplicate this particular query right here and do something more so I'm just going to go ahead and do something slightly valuable you would still not be able to connect the dots but in just a bit I again have a table the table has got two columns the letter column and the value column we obviously know that if I were to reference the table and after the table if I were to put the square bracket and reference the name of the column I will get the column as a list but not every single time you would want to have the value sometimes sometimes you want to want to pull the headers so I want to pull the headers out like I want to have the letter and the value extracted as a list W the column headers the column yeah yeah so I can make a step and there are several functions M functions available in power query that are going to be able to do more nuanced operation that deliver you a list for example if I were to just make a new Step that you can see it right here which is custom one I made a new step in the formula bar is currently empty but I'm going to write a function here like a formula here and the formula will give me these two values in the form of a list so let's just say that I'm going to write something like table. column names then you can see that uh it is asking me hey the table. column names function is asking hey which table are you trying to refer to so the table that I'm trying to refer to is nothing but the previous step which is right here and that is the table that I would want to defer to so I'm just going to say change type is the table which is the previous step now as soon as I commit to this function you're going to see that it also gives me a hint it says that once you commit on this form formula you're going to get the column names as a list so as soon as I commit to this the previous table all the column headers you have now received in the form of a list so if you take a look in the previous step we had two column names we had the letter column and the value column these two columns are pulled out and they are in the form of a list letter and value at this moment everything I believe is looking theoretical to you and you're probably not able to send okay so what like what are you trying to get at right yeah yeah okay we're getting there now let's just solve a practical question okay okay all right so o at the moment if you can see that I have two tables here this is my table one this is my table number two and I can obviously go ahead and combine the tables in our query right so I if I just maybe show you that table is called table two and the first table is called table one I'm just going to go over uh to this particular table right here click on data and I'm going to say hey from table range and the table is now there in the power query window right that's the table that we working with now let's say for example I want to append the two tables first table plus second table I want to append it together like a b c and then DF and then the first three numbers and the second three numbers this should become like one table which is very very easy to do so yeah yeah exactly so I'm going to start to use a bit a bit of M here but nothing that you you can't you can't you cannot understand so I'm going to get rid of the change type step right here and at the moment if you take a look at my source formula it's referring to table one which is the first table right here it's referring to this table but I want this formula this formula to refer to both the tables so table one and table two I don't want the table one want to be hardcoded here oh oh okay right so what I'm going to do is I'm going to get rid of this part of the formula like I'm just saying hey go to the current workbook and get many get as many tables as you would have that's what essentially I'm trying to do I press enter and you can see that this table one which was initially hardcoded is right now here and I can see the preview of the table so if I just maybe bring that up I can take a look at the preview of the table that's your first table on the left you see that that's the first table and that's the preview and that's the second table which is behind the window at the moment oh my so far so good okay all right I didn't know about doing this so if we had nine tables in this workbook we could get them right here like this that's right okay okay so here is where the magic begins now I'm not surprised Oz that you would not know of this technique that if you want to expand the tables you're going to obviously click on the expand yeah button so you expand the tables so what you're going to get is the two columns that are within the table but you'll also get the name column which is the name of the table which is fine unless you don't want that column right yes unless so if I don't want that column I can get rid of that I can press the delete here and I can get rid of that but let's say I want that column along so let's just click on the expand button that everybody knows about so I can click on the expand button I can uncheck the name prefix it gives me hey the two columns that are within the table are letter and the value and they're checked right now if there are any more columns I can click on load more obviously and then I'm I'm going to click on okay very standard exercise for anybody who has worked with power query before right so let me just do that so I click on expand click on okay and the columns are expanded are tables are combined so this is my first table and that is my second table the tables are combined append it good to go yeah yeah yes now there is just one tiny problem let's just say that I were to just go ahead in the Home tab and I were to say hey I want to close and load the data so I going to say close and load to and I would like to load the table uh right here next to this page so I'll just maybe load it right here click on okay and the table gets loaded and um I can just get rid of that one second uh so I can say hey just remove the nulls so remove the empties home close and load well actually what's happening is that it's referencing the table once again so now we have three tables on the page so table one table and okay wow that's fine but that's fine now if you take a look here here if I were to add a column to this table so let's just say that I cut it and put it on the right a bit and I add another column so let's just say that I want to add a category column and I say hey the first one is a high category then you have a low category and then you have again a low category now ideally speaking this table table one should expand to have four columns now so one of the name column and then you should have a category column in between but it will not do that automatically so I'm just going to go back to Power query and I'm just going to going to change the load Behavior I'll not load it in in um in Excel so I'll say hey I only want to make a connection click on okay yes the table is off from here so we now only see the two tables the first table and the second table I'm going to go back in power query and if I now go ahead and take a look at my table even if I were to go to the Home tab and click on refresh it is not going to get the additional category column in the table because okay because if you were to go back and take a look in the source I take a look at the preview of the table you see that in the preview I can take a look at the category column which column does not exist in the second table that's totally okay but as soon as I expand the tables the category column disappears because I were to I would now have to click on the gear icon yeah and then pull up the category column from here it's unchecked it's unchecked which is fine I mean checking the column is like no big deal but it's not Dynamic right the table adds a column your query should update and your query should also update the column now let's go ahead and take a look at the code so up until the source step I was able to take a look at the two tables and I I made sure that there is a category column that I can see I believe the problem happened on this particular step which is where I stopped seeing that additional category column all right so if I just click on the on the step right here if I take a look at the formula bar the formula bar is something like this I'm going to copy this code and I'm going to maybe paste this code on the notepad and that is my code I'll just format the code just a little a little bit so that it's readable and I'll help you understand what that is so that's my code the code says table. expand table column that's the formula used we don't really care about the function because power query UI is very intelligent enough to use the functions then it says hey what table are you trying to expand so we are trying to expand the table which was there in the previous step Source that's the table that I'm trying to expand then it says hey what column are you trying to expand so we trying to expand the content column and if you take a look at the source step you're going to see that the name of the column that we were trying to expand on which we clicked on the expand button the name of the column was content and that is the mention of that content column now if I were to just click right here if you remember these these are the two columns letter and the value that we clicked on here and then we clicked on expand and because we clicked on those two columns those columns are now hardcoded now if you have a hardcoded column name then obviously power query's mcode will not put in a comma here automatically and write the name of the next column it would not know that so o if you were to take a look at at only this only this what does it seem like to you well it looks like a list absolutely it is a list we got two lists here we got two values but a single list here so that's the start of the curly bracket well well I'm thinking about after the after that comma we got two lists that's right yeah so that's the start of the cly bracket that's the end of the cly bracket within which there are two values letter and the value and then the letter and the value appears one once again because that's the second list now let me first help you understand that why does it repeat like letter value letter value why does it repeat what purpose does it solve so um the thing is that in this particular function you're going to see that the first letter and the value are the columns that you are expanding on which are the two columns which are right here letter and the value these are the columns that you were trying to expand the second mention of the two letter and the value is just in case after expansion would you like to rename them so I could say that hey I want to expand the letter but once you expand it I would like to call it my letter so now oh after expansion it is going to be called as my letter okay but the funny thing is that this part of the formula this part of the formula the renaming part of the formula this particular list is an optional and you can get rid of that you don't need it it's fine so technically we are actually looking at just a three-part formula which looks something like like this so you have the name of the table which is right here you have the name of the column and you have a list of the columns that you're trying to expand that's it right okay yeah now the question is at the moment this is hardcoded I don't want it hardcoded I want this to be handled by power query in such a way that it becomes Dynamic and there is no hardcoding so it automatically picks up the next column in case that column has been added that's that's what is going to technically solve the problem right so having that in mind having that in mind I will go back to the previous step and essentially what I'm wanting to do is I would want to create a list and the list should have all the columns all the possible columns separated by a comma in both of these tables not just one but both the tables right so what I'm going to do is first of all comb I com combine the data of these two tables table one and table two I'll just combine it once I combine the data of the two tables then I don't want the data I just want the column names that's my strategy to get the column names so first combine the data without caring about what columns do you have once you've combined the data then just give me the column names off it let's do that I am going to right click on the content column and I'll say that I want to drill down what this is going to do is this is going to pull this column apart from the source table which is something that we've already done so we said that hey if you write the table name and in the square bracket if you write the name of the column you're going to get a list so this is also done by the user interface so right click drill down and that converts this into list and the list has two tables the only thing is that earlier you had a column containing two tables now you have a list containing two tables no big difference but now I can use a formula table. combine so table. combine says do you have tables that you want to combine so yes I have the first table and then I have the second table that I would like to combine but then it's also saying a very subtle aspect that the tables that you're trying to combine you should give me those tables in the form of a list so I made a list and within that list I have two tables and I just want to combine them if I did not do this it would not combine the tables because I have not made a list of tables it says that give me the tables but those tables should be in the form of a list right so I close the bracket press enter and those two tables are combined yes and we got the category we did get the category column now yeah it does not concern power query when it's combining table if the names of the columns are the same it will append them one below the other if the names of the columns are not the same it will create a new column right right table do combine the thing is that this is exactly what we wanted but the problem is that in the source step we also had the name column that we we wanted to keep which has now been lost we don't have the name column anymore we we want to expand it we still want to expand it because if we expand it then this column is going to be added along so what I'm going to do is now that I have combined the data of the two tables I'm not interested in the in the data at the moment I'm just interested here in this I'm going to go ahead and say something like table. column names and start the bracket and close the bracket and table do column names function is going to pick up this particular table and give me the names of the column so letter value and category but this time again I'm going to get nothing but a list by the way this is a list if you take a look at the formula we wanted a list this is a list but then the only difference is that now the column names are not hardcoded they are being driven from a formula and we now also have the additional category column present in our data okay okay all right nice so I'm going to relabel this as column name names this is my column names list I can just rename the step and I can click on the effects to make a new step and this particular step is now going to reference my source table which is right here so if I just going to go ahead and say Hey I want to reference the source table click on enter and I'm going to get now the two tables back on again which I can now expand so I'm going to click on the expand button do exactly the things that I did it before I even will uncheck the category column because I did not have it at the start so I just had the letter in the value column I click on okay the tables do get expanded I get the same code once again hey I want to expand the custom one which is the previous step I want to expand the content column the letter and the value hardcoded in the letter and the value to be renamed same stuff but only this time if I happen to just delete this part which is the renaming I don't want it but the letter and the value which was the column that I was trying to expand now I all already have those columns not hardcoded but created in the form of a list right so I can just go ahead and delete that and I can say he just reference the column name list close the bracket press enter you have the category column now present automatically in the data yes okay all right now I'm just going to go ahead and close and load the query I will not load it back into Excel for uh because that creates a duplication but now this time what I'm going to do is I'm going to name change the name of the column and I'm going to say something like hey this became let's say a color not a category but this became a color and all of the products are red colored and here I'm going to add a category column and then everything is let's say a high category all right now technically speaking you have two columns which are common so our resulting table should have two columns common and then the color column should come in here and the category column should come in here so now our resulting table should have four columns right right let's let's just go take a look so if I just go back and take a look at the table back in there you're going to see that the resulting table does have four columns it has the letter column the value column the color column which was only for table one and the category column which was only for table number two automatically how did it happen if you go back to the source step you can see that the preview does show me the color column here the preview does show me the category column here I then combine the data of these two tables in the next step so this particular table. combined function is going to combine the data of the two tables but I discard the data I don't want the data I just want the names of the columns so I use the table. columns names function to extract only the names of The Columns of for both the tables so now you can see that this is now becoming Dynamic we added the color column we added the category column it was Dynamic and we have literally replaced this list which was a hardcoded list with the dynamic list that we created okay I'm following and I'm thinking right so now I see I'm seeing a relationship between the M code and the list and because I haven't done much with the m code I wasn't paying attention to oh okay put this in a list and this other thing in a list I'm I'm very much aware of the hardcoding problem that's right excellent so that was one okay I just want to Maybe cover one more example because it'll just be a bit too much to consume all of that in one go okay so this is something very practical and you're going to have that coming up over and over again I'm sure you must have faced that sometime in the past as well so I open up another Excel that I prepared for you which is right here if you this this looks like exactly what I was thinking about okay let's go yes so if you take a look at this data this obviously is pivoted data so you if I I have to read the number 20 I have to look up and I have to look on the left anytime you see cross tabulation in data that's what I call it it's it's a pivoted data it's cross tabulated you can't understand 20 unless you take a look up and unless you take a look at on the left now obviously you can come in here I let's just load the data into Power query so control t for that click on okay yes this is my table and I'm going to say hey why don't you load the data into Power query from table range and that data goes inside of power query so first of good nothing that complicated now the thing is that if I were to go ahead and right click on the product and say unpivot other columns the problem is solved I can relabel this column as month I can relabel this column as something and I can click on close and load the data so let's just do that so Home tab close and load close and load to and I will load the data uh existing worksheet on this particular cell which is right here click on okay and that is the data loaded right right thing is that later somebody goes ahead and adds another attribute to the product so let's just say they add a class to the product right so these are these are all my high class products and these are all my mid-class products now back in power query you would have to go amend the step now you would have to right click on two columns and then say unpivot other columns because the first time when you did it you right clicked on just one column and then said unpivot other columns because if you were now to just refresh this particular query the class column is also going to be unpitted so right click and I'll say refresh you're going to see that the class column is also un pivoted which is something that I don't want it right how do we solve it let's take a look so I'm just going to go back to my table and go to the source step this is where I right clicked on the product column and I said unpivot other columns that's what I did right and that created the next step which is this particular step right now if you take a look at this particular step let me just copy the column and paste that right here and also kind of format this bit now if you take a look at this piece of code right here what this code is doing is that it's saying hey which table do you want to use for unpivoting I want to use this particular sorry I want to use this particular table for unpivoting which is nothing but my source then it says did you click on a column and said unpivot other columns yes I clicked on a column called the product column and I said UNP other colum oh my gosh okay right so the product column is hardcoded here and the other two standard columns that we always get are attribute and value and those are attrib and that's the value so the only change that I would like to make is that here now manually I would like to add another one called class and that will probably solve the problem but the thing is that next time another column gets added I'll have to come back to the code again and then revise my code once again right right so so like so like you got your product and then you got your class and then maybe you add a a vendor that's right anything yeah anything that's right now again what does this seem like to you that is a list that's a list that is a list so you have two items in the list at the moment product in the class but those two items are hardcoded at the moment and we don't want them to be hardcoded so now if I just if I just go back to the source step this time I want to be able to build a pattern meaning anything which so obviously I want the names of the columns once I get the names of the columns anything which is not a date anything which is not a date so this is not a date and this is not a date then needs to go ahead and make the part of the formula so these two are not dates and these should be dynamically put in right here so I need to write some logic and the logic should check for what please do unpivot only the dates keep the non dates capture them dynamically and put that right here that's the logic that I'm trying to build let's just do that so I'm going to go ahead and make a new Step so I click on the FX click on insert a new Step gets inserted in the new Step what I'm trying to first get give me all the column names no matter they are date they are text it doesn't matter just give me the names of the entire columns in this table so I'm going to go ahead and start to write a formula table. column names start the bracket close the bracket we've done that like two or three times now and that should seem okay and this has resulted me in all the possible names of the columns in the previous step all right o here is where it gets interesting and also slightly easier for you because you are the UI guy the thing about lists is that as soon as power query realizes that you're working with a list in this particular step it realizes that you're working with a list you're going to see that if you could go over to the Home tab all the options that we typically work with our grade out you also going to see that here there is no filter option to filter the list nothing nothing because power query's UI was built to work with tables and not lists so if you take a look at all of these tabs everything is grayed out everything is grayed out and so on and so forth but that's okay because what we can do is now in this particular list tools which only appears if you're working with a list we can convert it back to a table table yeah yeah right so I can just go ahead and say that hey this list that I have made please put it back into a table and I'm going to insert a step in between that's fine and that becomes like U uh just show as errors or maybe I just don't want to have the extra columns no delimiter click on okay and that becomes a table now if you go back to the hes Home tab and stuff like that you're going to see that everything is back up again and you can work with the table now it's very easy for us to be able to see that hey which are the date columns and which are the not non-date columns I'm going to go ahead and create a new column so I'll make a new column right here and then I'll try to convert one Jan into a date one Feb into a date so on and so forth that's what I will do so I'll just go ahead and make a new column so add columns tab create a custom column insert a custom column and I'm going to write a formula and the formula is nothing but date. from so I want to take the date from the column one so I'll just feed column one close the bracket press on okay and you can see that hey one Jan was converted into a date no problem one Feb was converted into a date no problem so on and so forth nothing that complicated but the product and the class actually gave me errors that's what we want that's exactly what we want so on this table what I can say is that hey why don't you keep the errors and delete the rest I think that option is there at the Home tab uh keep rows keep the errors uh insert a step in between and that's exactly what we have product in the class the only problem was if you take a look at your formula your formula wanted product in class you have product in the class the only problem this is not a list but this is a list put it back so how do we how do we get this into a list like this easy enough well yeah yeah well I don't know so I know that you can convert a column into a list exactly so if I were I don't know if we gota I don't know if we got to delete the custom first you don't need to delete the custom okay okay right delete all that I'm going to do is I'm going to say that in the next step in the next step I'm going to say kept error which was the previous step mhm yep so that's my table name I'll put up the square bracket and I'll mention the name of the column that I would want to extract as a list so square bracket column one is going to give me a list and that is the list that I would want there you go now I can call this as unpivot column list I can kind of rename the step now if I go back to the function the function has broken for good reasons because we added so many steps in between so we got to we got to fix that so I'm going to go ahead and say that hey if you take a look at this formula right here table do unpivot other columns was working on the source step not on this step so we need to change this to Source because the source contained the table that you wanted to UNP it and then we also if you take a look at this we wanted to have two inputs here not just one so product and the class and these two inputs now are coming from this step that we have made mhm so I can cancel this out the hardcoded entry and I can say that here is where I will mention this particular unpivot columns so unpivot column list uh attribute and value Remains the Same so far so good press okay and we now have two columns as un pivoted right go home close and load the data and that's your data you can see that product and the class are now there pretty good now if we if you were to just go ahead let's just say that uh cut that paste that right here now if you were to just go ahead and say that hey this time I want to add a vendor and let's just say that um the vendor is something like hey let's just split it so you are the vendor for the first three I'm the vendor for the next to now ideally speaking power query should now select three columns automatically and then say unpivot other columns right so if I were to just take this table uh put it on the side here not on the left but put it on the side and then do a refresh so right click oh oh okay and do a refresh you can see that now it also has the wender automatically show up as unpivot other columns in the list wow okay okay whenever you're taking a look at the M code in the advanced editor so this is the advanced editor at any point in time at any point in time you see the curly brackets and you see values within that Curly brackets that is nothing uh that is nothing but a list so this is a list again this is a list right so essentially what you would want to do is if you have the curly brackets and something in red the less you have red the less hard coding you have in your M code yes so reduce the red is one thing that I say it very very often and I'm writing this down okay okay so wow yeah I mean if you have any questions happy to help I'm just thinking there there's that issue also of of splitting columns and I don't know you have a column with delimiters and then you do split columns and then say it was five columns po query will hardcode that five that's right and then you add three more columns and it's stuck on a five that's right and you got to go do something to get it to recognize eight so now is that a list issue yes that's a list issue but that's slightly more trickier than that so let's just go expl that as well I would not start to write the code right here but then I have a video on that maybe I can just put the link here in the description for everybody to see but I'll just help you understand so let's say because I've I've done videos on this so I I have I have solved it but if I use the list in the video I didn't understand it or I did some roundabout um interface way of doing it okay so cool so let's just say that we have a column and the column has letters so I'm just going to say a comma B comma C and then here you have a comma uh b a comma C and I'm going to convert this into a table right now sorry I'm going to convert this into a table my table has headers click on okay right now the max that you're going to get is three columns so first column for a second column for B second the third column for C right but then if you come Ahad and increase the number of columns you just write d as well then the query is not going to have d as a new column because that is stuck to what happened in the first time I'll just help you understand it it like and then not do that but I'll just help you understand it so I'm going to go ahead and load this data into Power query so click right here data from table range and that goes inside power query uh Delete the change type right click and I'll say split the columns by a d limiter my D limiter happens to be a comma uh at each occurrence click on okay and that is what you get uh delete that as well now if you take a look at this piece of code in this piece of code it says Hey table do split that's the function which table are you trying to split so I'm trying to split the source table which column are you trying to split so I'm trying to uh split the column so this particular column it's called column that's the column that I'm trying to split then it says here's the formula uh split text by D limiter my D limiter happens to be a comma once you split it then I want you to make three columns column 1.1 um column do2 and column do3 these are hardcored you see that now now if you're somehow able to to take this particular list and make it Dynamic we're done the only trouble is that we need to go ahead somehow count how many splits do we need through the commas and then that count like four commas or five commas or six commas that count is then going to determine how many items that you're going to have in the list that's how would you would solve it but then I have a video on it um split columns by a d limiter like make the columns dynamic and just put the links down but that's the logic of it I'm following because it sounds like we've got this hardcoded call One Call two call three if it goes out to eight we could create a list somewhere else that goes out to p.8 that's right and plug it in here that's right yeah so you can have multiple steps here yeah uh that will Define the logic as to how many columns do you would want and eventually what you're going to have is a list and that list will have as many columns displayed dynamically and that list will then become a part of this formula yes oh my I'm getting it I'm getting it when we saw the red when we looked at the code and could see the curly braces and now I can identify lists that's right from other things right that's right that's right and I like the way you built up to just show the technical stuff and then we got into in one way you know creating variables we're dealing with the the hard code in this particular query that we have created just a while ago this particular query which is where we were trying to do unpivoting of the first two columns or three columns and leave the leave out the rest and we did so many steps all of these steps to only get to this particular list if you were to Know M code like like right from scratch you can literally omit all of these steps and just have it within the unpivot step only like everything you can just code it through but then I was just trying to make it easy just to kind of help yeah so you have a list you cannot work with a list in the user interface so what do you do you go to the transform make it as a table so once you make it as a table the the power query UI lights up and you can now do anything that you would otherwise do with the data and I'm just trying to do is that hey I'm just trying to find out that where do you get an error so here is where you get an error and we keep the errors and then we convert this back it's a table at the moment we don't want a table we want a list we convert this back into a list and then we plug this particular list into the formula which is right here and I love those tricks where you get some things to error out so that you can either keep the errors or keep the non errors but yeah that's a way of of parsing data really slick um but yeah and I did not know that the the you know when we saw originally the two things and you did the my list I did not know that that was just the renaming I thought it was because we had two tables that had the same columns but then okay so that made sense to me so whenever you're trying to expand where was it here expand so in the expansion step uh right now we have converted that into a list but then if you put a comma and have another list the second list is going to rename the columns so yeah well you could also have the second list as Dynamic for all that matters like expand but while expanding just also rename the column so you can of save another steps wow I I got you I got you I'm sure I've exceeded the the threshold that we had with for 30 minutes but I hope you were able to get some value out of it uh the list especially reduce the fear a bit maybe yes yes yes I I follow I follow because you did a drill down I'm familiar with using drill down to create variables yeah yeah you create a valuable that way passing a parameter uh so I followed all of the pieces you helped me to connect the dots and understand the place of a list sure and now I just got to go back and think about some of the ways where I just blindly followed instructions and created lists and kept moving now I have a fuller context as to what's going on yeah I appreciate that thank you thank you are you are you still going to sleep at 2:00 tonight probably probably man but uh I appreciate this a lot a lot this opened up so much more for me to go back and look but yeah they aren't so mysterious now yeah I mean uh I would not deny that once a power query user interface guy takes a look at the M code be in the formula bar or in the advanced editor it looks so godamn scary I would not deny that like like yes what are all these characters why are there like four different types of bracket and why there is a hash sign and all of all of that but it's fascinating what you can do with the m language where the user interface kind just stops or it becomes relatively very hard to do it with the user interface and what could you do with the m language because Microsoft has built this UI and the UI is absolutely phenomenal to get you to 80 90 95% of what you want to do and automate that but every once in a while you're going to have that roadblock where you want to do something but it doesn't just work with the UI you just need to go a little extra because the UI is gonna hardcode things right yeah yeah it does you know I have done this trick of uh split into rows because that will expand yes that's but then I've got to put it back later so I've got these long and multiple queries and they work they work that's right right um so I am so grateful for the UI as it is and I've I've not been stopped by it um but I can see now a lot of things could be simplified absolutely by using the list and creating these variables this way yeah list just happens to be one object type so you have lists then you have records uh then you have data types um tabl yeah tables of course you have tables and then you have custom functions on top of that like this is and if you put together all of that and like recursiveness is another thing that is there like you have self referencing functions another thing that people often get very very scared about is the each and the underscore keyword uh so you often are going to take a look at the M code and it's going to say each underscore at in the in the the formula bar I don't know if you've seen that or not not that coming to mind yeah especially when you apply filters to a table you're going to see each underscore and then something like each something um so that is where uh people kind of get scared or something or especially if you create a new column if you create a new column in power query uh I think we did create a couple of new columns did we uh so yeah we did so we got a list we made the list into a table and then we added a column right we added this custom column now if you take a look it shows you each okay right so uh there is something called as iterations in power query that often confuse people a lot so I'd agree the language is not very user friendly it's not easy but then once you get the hold of what you're doing and the objects and how do you work with different objects in B query um it's a very very fascinating language it's a very fascinating language I like this cool I like it yeah well thanks for this really appreciate it absolutely it's such a pleasure such a pleasure that we able to do this uh yes and thanks for offering and and keeping up because um we had this conversation more than a month ago yeah and then I went to the MVP Summit and I went back to Portland for a few days and then I finally got back and we we we it didn't slip through the cracks yes yes yes yes yes okay all right well thanks again for this I really appreciate it and I follow it and I'm G to have to play with this absolutely absolutely you'll have the video out so you can maybe watch it again yes all right cool thanks a lot absolutely thanks all for making time and I would love to have you sometime once again oh yes let's make it happen yeah yeah yeah

Channel: Goodly

Views: 15,594

Keywords: Goodly, Chandeep Chhabra, goodly power query, Oz du Soleil, Power query case study, lists in power query, Lists in Power Query, Power Query Tutorial, Table to List, Excel Formulas, Excel Data Analysis, M in Power Query, what is lists in power query, lists in power query tutorial, Global excel summit, in conversation with, ICW

Id: qlbU3jUsQAs

Length: 54min 59sec (3299 seconds)

Published: Wed Apr 10 2024