power query's user interface is absolutely phenomenal and an average user wouldn't have to go beyond the user interface for 95% of the data problems but every once in a while you're going to come across a problem that is going to make you scratch your head and you're going to say that how am I going to solve this problem let me bring to you such nuanes and seven incredibly powerful power query functions that help you go beyond the user interface no further Ado let's start I'm going to divide this video into two parts part number one is going to be accessing UI functions meaning some things which are already built into the UI but if you tweak them a little you get fantastic results and part number two is going to be Beyond UI functions functions that are just not available in the user interface of power query and that let you do a lot of good work of data cleaning let's just start with the UI first all right the first function that I'd like to speak about is the table. add columns function and you can obviously get that through the user interface of powerquery let's just take a look so I've got a simple table and I'd like to add a column let's just take a look at the behavior of the function and then how can we modify it to do some cool work with data all right so I'm going to go over to the add columns Tab and sure enough here we have the custom column clicking on that gives me this custom column box and let's just write a pseudo one number in there so if I just happen to write one and click on okay you're going to see that we definitely get the table. add column function automatically added because we clicked a bunch of buttons in the power query user interface now if you were to take a look at this function this function G gives you an ability to add a column but if you just carefully study the different arguments of the function you're going to see that it has got a few interesting applications so the first argument of the function is hey give me the table that you're trying to work with on which you're trying to create a column and that is nothing but the previous step then it says hey what's the name of the column which is right here and then it says the function to create the column this function simply means what formula do you want to put in every single row of the table that is going to form the value of the column now at the moment I'm saying that in every single row write one but what is going to happen if I write a blank function note that the last part of this argument has to be a function it cannot just be a value so if I just put a comma you're going to see that the last part is nothing but the column generator as a function and one way to write a blank function in power query is just to write the each and the underscore keyword if I just write the each and the underscore keywords you're going to see that in every single row what you get is a record and record means nothing but the row of the table so here you get the data of the first row of the table you can see that all of these values kind of are right here in the first row and then here you get the second row the third Row the fourth row so on and so forth now once you have undercovered this ability of this function to go row by row and in every single row it produces a record then you can pretty much do anything with the record giving you an example so let me just go ahead and start to modify this Behavior at the moment we have an underscore right here and that produces a record now I would like to transform This Record into maybe a list so I'll say record.
two list and I'm going to start the bracket and I'm going to close the bracket and I'm going to click on okay and sure enough this actually becomes a list now once you have a list you can skip the first item off of the list and maybe total all of these numbers up so I can just go right here and I can say now that I have a list I want to skip the first item of the list so I'm going to use list. Skip and that is going to skip the first item of the list by default because list. skips the first item of the list by default and then I'm going to sum this list so I'll say list.
sum start the bracket and close the bracket towards the end and now this is just going to give me the sum sure enough you can just go ahead and change the name of the column right here call this as a sum and this is absolutely Dynamic now the point that I was trying to make through this was that once you have undercovered that table. add columns function is going to generate a record through a blank function in every single row of the table using that record you can pretty much do anything that you would like and that's how you enhance the capability that is not available in the user interface in case you're enjoying the video thus far you're absolutely going to love my new book on power query power query beyond the user interface in the book I talk about Advanced Techniques that go far beyond the user interface and teach you what lies behind those clicks of power query especially using the M language to solve more harder and nastier data cleaning problems the initial few chapters are based on the fundamentals understanding those basic building blocks like lists records working with tables manipulation of the data and more advanced chapters cover like iterations custom functions nested tables and a whole lot of more complicated aspects that take you to the next level the interesting thing about the book is that the book has been written in such a way that it contains no joggin I've used a very very friendly language to explain the most complicated Concepts in a fun way and the book contains a lot of diagrams and images to help you cruise through the book as if you were just watching my video but on print if you buy the book before the 7th of February I'm also very happy to give you access to special bonuses which is nothing but some Advanced functions in power query custom functions in power query and advanced case studies as well note that these bonuses are worth $150 and you're going to get them absolutely free along with the book in case you order or pre-order the book before the 7th of February I've poured in a lot of care and attention and love into this book and I hope that that you thoroughly enjoy the book grab a copy today let's explore the next pivoting function in power query and how can you enhance be the capabilities of the user interface take a look at this very interesting example and then I will talk about the function real quick so I've got this uh three columnar table product color and the region and maybe I'd like to Pivot the data let me try to show you or give you a glimpse of what am I trying to get at so I'd like to have maybe the products here so products and I would like to have all the unique products here so let's say product a b c and d and then I'd like to have the regions in the column so you will have maybe region number one maybe let's just say North and then the region number two is let's say West so I will have these two regions and matching these two values let's say product a and region n which is north I'd like to mention all the products or all the colors of the products right here so howsoever many number of colors we have I'd like to mention underneath that how do we do such a kind of thing obviously this is pivoting because we are converting the regions into columns so we will have to Pivot the data let's just try to take a look at how that is done in power query so I'm going to go over to the region columns right here and then I'm going to go over to the transform tab right here and in the transform I have called something called as pivot column if I happen to click that it brings up this dialog box which is where it asks me a question it says that the column that is being selected I'm going to make columns from that so North is going to become one column and West is going to become another column then it says that hey what would you like to put it in the value so if you remember the pivot grid we had North and South here we had the names of the products here so a b c and d and this section which is the combination of the regions as well as the products is called the value section of the pivot table now what would you like to put in all of these values here so I would like to put the color in the values and that's where I will just mention that everything which is remaining in the data set is going to form the rows of the data so obviously product is going to form the rows of the data I'm going to maybe click on okay and now what we get is nothing but the products as well as the regions right here now sure enough we do get the regions but underneath those regions in terms of values I don't see the name of the color I actually see the count of that let me just go ahead and modify my aggregation for a quick second so I'll just go back and I'll say that in the advanced option I would like to maybe do not Aggregate and just mention the data just like that I click on do not aggregate I click on okay and we land up with some errors so we have an error here let's just go take a look at the error it says too many elements in the enumeration to complete that simply means that for for the value a and for the region north there must be multiple values and multiple values cannot be fed right here let's just go take a look at the data so if I just go back to the source data if I take a look at a and I take a look at a and I take a look at North and North you can see that we have two different values right here red and white and therefore both the values cannot fit in there now how do we solve this problem now let's just go take a look at this particular function so this function just a while ago was working when the summarization was a count and it was not aggregation so let's just revert it to that so I'm going to right here and I'm going to say hey why don't you just maybe in the advanced operations do count that's fine click on okay and this starts to work fine now at the moment if I just just go take a look at the function it says table. pivot on which table would you like to apply the pivot table too so that's my previous table which is nothing but Source hey give me the columns right here and these are nothing but your columns and then it says the color happens to be something that we will put it in the values and then it says list.
count so if you take a look at the behavior of this particular function in the last argument of this function you're going to see that the aggregations should come in again as a function that means howsoever you want to aggregate the values that should come in as a function and we now know the default behavior of writing the function and that is nothing but the each and the underscore so rather than actually writing one of the Native functions of power query I'm going to go ahead and write the nich in the underscore keyboard and let's just take a look at what happens so if I just do that you're going to see that we end up with a list and if I just maybe peek into the list I see two values now that we have undercovered that the table. pivot function in the last argument has a list by default we can just combine the values of the list and now I can go ahead and I can say that I would like to do a text. combine I'll start the bracket and that underscore is giving me the list and I'd like to have a d limiter which is nothing but a comma and a space I'm going to close the bracket in the end and this is exactly what we wanted another very interesting function in power query is the table group function that can summarize the data but it can go a lot Beyond the user interface provides you with take a look at a simple example that I'm trying to work with so I've got products and the units and the price in a single column that means that the product is mentioned twice the first row is nothing but the units and the second row is nothing but the price again product name c we have the units and the price and product B units and the price and we would just want to have the total sales so I would like to write something like this a and what is the sales for that which is nothing but the units into price and then I'd like to write C what's the sales for that units into price so on and so forth for the entire products now obviously that is a summarization that I have to do but let's just take a look at how the default summarization Works in power query so I'm going to go over to the transform Tab and therefore I have the group by feature I'm going to click on that and then I'm just going to jump over to the advanced and I'll say that I'd like to summarize by the product sure enough I would like to create a column called the count doesn't matter for now I'm just saying count the operation is Count rows that's fine click on okay and therefore we land up with this now every single product did have two rows of data and that's what it just shows if you now take a look at the table.
group function and the way it behaves is that you're going to see that the table. group function asks you for a table hey what table are you trying to work with I'm trying to work with the source table then it says hey what's the column that you would like to summarize and I'd like to summarize the product column all right then it says the summarization is going to have three parts of its own so it's going to have the name of the column which is right here and then it's going to write a function through which every single row is going to be summarized and the data typee which is applied which is nothing but the number now let's just go start to tweak with this function so if I just happen to delete the comma and read the arguments of that it says again the aggregations as a list and because we know that this is using a function each and the underscore somehow let me just kind of break the table. row count function that counts the number of rows and just let me light a blank function let's just see what happens the max that it is going to do is break the query but here I'm with you so I'm going to go ahead and say each underscore right here and press enter and what this is doing at the moment is that this is producing a table and you can do pretty much anything with the table once you've understood that for every single column that you create what you get is an underlying table for all of the rows of a if I happen to peek inside of the table you're going to see all the rows that match a and the units and the price for that at the moment I just have to take these two values and multiply them now of course we can extract a list from a table and the easiest way to do that is that you write the name of the table in this case the name the table is underscore and then I'm going to write the name of the column which is units and then I can write the price and therefore I have extracted a list now even if you happen to remove the underscore it is still going to work but let that stay for a bit now I'm going to say that I have a list and I just want to multiply the values of the list now I can write something like list.
product I can start the bracket and I can close the bracket here in the end and of course one more thing that I'm going to do is this is not going to be account but instead this is going to be my sales and now I get the sales value how awesome is that in case you're liking the video thus far you're going to absolutely love my courses on power query Dax data modeling and especially the M language which is where I break down very complicated Concepts around data cleaning and ETL and then trying to help you understand that how do you build or how do you frame logic to be able to build your Solution on your own data these are extremely structured courses which is where I take students right from scratch build up the fundamentals and then we go on start talking about more advanced more complicated problems hundreds of students have joined my courses and they have left some raving feedback about the course in case you're interested to take your skills to the next level I'll H encourage that you take a look at the courses and they will be super beneficial thanks so much another very interesting function in power query is skipping the rows from the table and it brings to you several options which are obviously not there in the UI take a look at this junk table that I'm working with and clearly I can see that a few rows up on the top of this table are junk and I want to get rid of that sure enough you know how to do that you can go over to the Home tab and in the Home tab you're going to find that we have something called as remove rows sure enough I can click on remove the top rows how many rows from the top I can write the number three I'm good to go and the table is all good now the problem with this particular Approach at the moment is that the number three has been hardcoded right here because the user interface of power query does not allow us to write a condition the problem with the three is that next time you have four junk rows or two junk rows you're definitely going to lose some legit data and I don't really want to write three here but I want to write a condition let's just take a look at first this function accepts a condition or not so if I just happen to delete the three and read the intellisense of the function it says hey give me a count or give me a condition so sure enough this does accept a condition now the thing with conditions is that a condition always have to give you a true or a false and therefore this formula is only going to work that means no matter what you write here should have to give you a true or a false otherwise this formula is not going to work so we got to write some condition here that gives me true or false but let's just say you and I don't know how to write that that condition right so let's just go ahead and take help from the user interface of power query so I'm going to get rid of this particular step and I'm going to make a new column and in the new column I'm just going to go ahead and happen to write the underscore we just saw a while ago that the table. add columns function when created through the custom column produces a record in every single room for now let's just click on okay and that produces a record and the record can obviously be converted into a list so I'm going to go ahead and this underscore that I have created or a record that I have created I'm going to transform that into a list using the function record. to list and I will start the bracket I will close the bracket press enter and sure enough this actually becomes a list now in the list right here I can make a simple check and the check is that keep removing the rows till the time you find any value null so you can see that there are two values null sure enough this cannot be the headers because the headers need to be filled with all the values that's the assumption that I'm making so I can go over to the next one again you have null values next one again you have null values keep removing till the time you have have the null rows now how do I produce a true and false because if you remember the table.
skip function is asking me for a true and false so I can go ahead and start to write something like now that I have a list so I can say something like list do contains and I can start the bracket this is my list and does it contain a null or or not and as simple as that I click on okay and this actually produces a true or a false output now I know that I have been able to write a function that produces a true or a false output that I can feed into the table do skape function what I'm going to do is I'm going to take this each part and all the way till the place where the function ends contrl C on that and I'm going to delete this particular step I delete it and again I'm going to go over to the Home tab and I can say that I want to remove rows remove top rows and sure enough I want to remove the top three rows of the data that brings me the hardcored three which I will cancel and I will feed my formula right here now what this formula is saying is that go in every single row and perform the same check that we did it while adding a column but the only difference is that while we were adding a column we could have seen the trues and the falses now we can't see it the rows are just going to be removed it doesn't change the output because of course it has removed the first three rows you can now write a condition instead of being stuck with the hardcoded numbers in the table do skip let's raise the bar and move to part two which is where we're going to take a look at functions which are not available in the user interface of power query and you just have to understand the behavior of how these functions are going to work let's start with this so I've got this simple table right here and the table does not have any headers so it's column one column two and column 3 and I'd like to give some headers right here now of course I can just go ahead and rename the headers so column one could be renamed as product this could be something and this could be something but I don't really want to do that you going to see that we already have another query right here called query 1 which is where we have three values and these are the three values that I wanted to put it on top of that table as headers that means the headers are going to come from here and the data is going to come from some other query this is going to be a common scenario while doing some Advanced power query operations how do you do that I'm going to talk about two functions which are my absolute favorite that are going to get the job done take a look so I'm going to go back to my query and the first function is actually going to break the table apart into three different lists so this is going to be list number one this is going to be list number two and this is going to be list number three so every column gets converted into a list right let's just do that so I'm going to maybe make a new step and in the new Step I'm going to say table. two columns table. two columns function simply takes the table which is nothing but the source table that that that is nothing but my previous step which is this particular table right here and it is going to take the columns of the table and convert them into a list if I now commit on this formula you're going to see that the table is broken and it has now given me three different lists this is not a table anymore now what you can do is you can take these lists and put it back into a table so I can now say that I've got these three lists and I can put them back into a table so I can say hey table Dot from columns I can start the bracket and I can close the bracket and now this is actually going to convert it into a table again but you're going to mock me at this state because where we started from where we are at the moment are not different places they are exactly the same output so how have we made any difference so what this function table.
from column does is that while you're putting the table back together from the lists it allows you an additional input to declare The Columns of those lists or not and that's exactly what we're going to do so you know that we've already got the columns right here in query number one but these columns need to be in the form of a list so let's just convert this into a list I can right click on the column and I can click on drill down this actually becomes a list let's just call this as columns and I'm going to go ahead in 56 query and say that hey the names of the columns are going to come from this list right here this is nothing but columns and now we are good to go and that's my ID that's my color and that is my region so these two functions table. two columns and table. from columns are extremely beneficial to break apart the tables and then do whatever with those tables in as an interim operation and then put them back together with nice column headers my last and my absolute favorite function is the list.
transform function if you have a list and you'd like to do some custom Transformations on that it's a brilliant function to learn because list Transformations are not available in the user interface of powerquery take a look at this so of course I have product Jan Feb and March and maybe I'd like to unpivot the data so I'm sure you know how to do that right click on the product you're going to say unpivot other columns and sure enough the data is unpivot the only problem is that because I right clicked on the product column the product column is hardcoded and therefore this is going to cause a problem should there be any other column added right here which is not a month so let's just say that you add another column called the category column and that now you're supposed to actually click on two different columns and then say unpivot other data and that is not going to be done automatically so I want this column not to be hard fed and I want this column to be automated that means this one should be automated and if you take a look at this this is nothing but a list and that's where we're going to use the function list. transform so let's just take a look at it I'm going to get rid of this particular step and I'm going to go ahead and first extract all the names of the column so I'm going to say table do column names start the bracket and close the bracket in the end sure enough we get all the names of the columns and these columns are nothing but a list and now is when I can start to transform these columns and my transformation logic is going to look something like this because this list then pick up every single item of the list and try to convert them into a date if you are being converted into a date I don't want you but if you're not being converted into a date then of course I want you so I'm going to go ahead and say something like this I'm going to say that I will have a list that I'd like to transform so list. transform I'll start the bracket and that is nothing but my list and the transformation for the moment is nothing so I will just happen to write a blank function close the bracket press enter and nothing happens to the list now I'm going to go ahead and attach a one so I'm going to say something like hey just take that number or the value or the text whatever that is and attach a one to that so one product one genan one Feb one March all right now convert that into a date so I'll say date.
from start the bracket and close the bracket and press enter and we are good to go now of course uh this returned me an error because one product cannot be converted into a date sure enough these can be converted into a date and they are good to go now what I'm going to do is I want to keep the errors I don't want to delete the errors I want to keep the errors but unfortunately there was aun function in power query to keep the errors of the table but not off the list so one option is that you can convert it into a table and then keep the errors but we want to continue working with the list so what do we do about it so I'm going to go ahead and say something like try just try working with this value and I'll just maybe kind of commit in this you can see that once we do the try keyword everything gets converted into a record and let me just explain to you real quick if you take a look at the first value which had the error you're going to see that the has error column in the record returns a true that means it has the error and if you take a look at the second value it does not have the error and you see the value itself now we're going to use these trues and falses so here we have a true that means the error was found and here we have a false that means the error was not found now what we can do is we can go ahead and say that this particular try keyword returns me a record and the record has a column called the has error column which has the value true and false let me just get that so I can go ahead and reference the has error column and that actually gives me the trues and falses of course now we're going to play a hack now at the moment if you remember we wanted to keep the values with the trues and not the falses that means the non dates now the list. transform actually converts the values into Tru and falses I don't want to see the conversion at the moment I just want to keep the values so if I just happen to cancel the transform word and instead use a list. select keyword I will be able to just select the values that were returning the true in the second part of the function because list.
