Transform Multiple Columns at Once in Power Query | Advanced uses of Table.TransformColumns Function

in today's video i'm gonna talk once again about the table dot transform columns function i have done a video on that in the past in case you have missed that video for any reason i suggest that you take a look at that i'm gonna be building on top of that video and talking about more sophisticated application what are we doing today we are going to be applying transformations on multiple columns in power query just as once using table dot transform columns it's going to be a slightly more advanced application but i'm sure you're going to enjoy this a lot no further ado you and i let's go all our people i'm in excel power query that's where i have loaded some very simple data let's just take a look at the data and the problem that i'm trying to solve simple four columnar data here column one two three contains some letters and the fourth column contains the value the transformation that i'm trying to apply is convert all of the letters in column one two and three into a lowercase so if i had to do that manually sure enough what i'm gonna do is right click on the column i'm gonna say transform and transform that into a lowercase now the function that gets generated is table dot transform columns right now if i had to do that for one more column if i just come right here on the second column transform that to a lowercase as well the problem however is that every single time i select the column and i do the transformation into a lower case the name of the column gets hard coded in the formula manually take a look column one is a manual text right here this is also a manual text right here that means tomorrow if the name of the column changes or any more columns are added then all of the work needs to be redone in the query to fix the query and debug the errors that is something that i don't really want and i also want my query to be scalable that means tomorrow if i have more columns which contain the letters i would want all of the columns to have the transformation of the lower case done automatically at once and i don't have to do one by one again and again how do i do such a thing now what i'm going to do is i'm going to quickly explain to you the structure that we're trying to build that is going to help us achieve that automation and i'm gonna jump over to a notepad real quick now the same formula which is up on the top i have copied that formula right here and let's just for just a second take a look at the structure of the formula and i'll help you understand that what exactly are we trying to build now in this particular table dot transform columns the first part is nothing but the source source is what the name of the table in which we are trying to apply the transformations so if you just take a look this is nothing but the previous step which contains the entire table with three or four columns now if you notice carefully every single column that we have mentioned as a transformation is going to be packed into a list take a look this column which is column number one and the following transformation is packed into a list so there's a curly bracket at the start there's a curly bracket at the end and within those curly brackets there are three essential parts so first is the name of the column the second is the function and the third is the data type and then once you we try to do that transformation on the second column as well we again have the three parts same parts called name the transformation and the data type now the most important part to realize is that we just can't write these three parts in isolation we have to back them into a list you're also going to see that all of these lists which are the first column transformation the second column transformation and as many columns as you would have these lists eventually further get packed into an outside list right here so we need to have a structure something like this we need to have like a list and in that particular list we need to have some smaller lists and every single list needs to contain three parts what are you going to contain you're going to have the name of the column you're going to have the transformation and in case you are trying to apply any particular data type and once you kind of create this particular structure what structure the list of a list structure so an outside list and then inside list which contains all the transformation once you're able to create a structure like this and we input that particular structure in here instead of writing this manually hopefully we would be able to automate the stuff that we're trying to automate to be able to create that structure what you need to understand is another function called list dot transform which is going to help us to create this particular structure so just for maybe a minute or two i'll explain you that another function list or transform and once you understand that we'll try to come back to this problem and start to solve this problem stay with me all right let's just take a look at the list.transform function for just a quick minute to help you understand that as the first step of the query i have created a simple list and the list contains three letters in capitals a b and c and to convert that into a list i have surrounded that with the curly brackets and that converts those a b and c into a list format pretty good what am i trying to do i'm trying to convert every single letter into a lowercase how do you do that i'm going to create a new step and i'm going to use the function list dot transform so i'm going to come right here i'm going to write the function list dot transform and you can see that as the first part of the function it asks you hey which list would you like to transform so i would like to transform this particular list which is nothing but the first part of the query or the first step of the query now this is the list that i would like to transform the transformation that i'm trying to apply on that list is convert every single letter into a lowercase so how am i going to write that i'm going to write something like take each value or every single row and why don't you convert that into a text dot lower right now the underscore means every single value of that list and text dot lower is going to pick up that value and convert that to a lowercase if i now commit to this function i'm going to get every single letter into a lowercase pretty good now we have been able to solve part of the problem here if you now take a look at the output that we wanted to get we wanted to get an output which is where we wanted to we have a list of a list so you can see that we have we do have an outside list and that solves part of the problem we do have an outside list as of now but as of now inside of this outside list we still have values inside so a is a value b is a value and c is a value however the structure that we are trying to get is that we need to have an outside list but inside of that list we again need to have another list that contains for the elements so the abc needs to be converted again into a list how do you do that i'm going to come back to this function and i'm going to say that the text dot lower function gives me a single value output however i would want to have the output in the form of a list so why don't we surround this function into a curly bracket and that hopefully should deliver us the output still abc but into a form of a list if i now commit to this i still get a you can see that i still do get a but that a is now packed into a list so we now have been able to get the exact output that we're trying to get which is nothing but the list outside list which is nothing but right here and we have three lists inside which uh contain you know some function that is going on as of now the only part which is missing is that if you take a look at this list right here this list contains three parts the first part is the name of the column the second part is the function and the third part is the data type as of now we have been able to get only one part that means that inside of the list we have just been able to get text.lower we have not been able to get the name of the column we will solve that problem once we kind of go back to the case now let's just go back to the case and start working with table.transformcolumns and list dot transform to be able to create this structure all right with the logic out of our way let's just start to build the solution the first thing that i would want to build is try to automate the names of the columns which are hard coded in my formula as of now so column one and column two are as of now hard coded i don't really want to have that so what i'm going to do is i'm going to create a list and only call the three columns that i would want to have leaving the possibility that in the future i could also extend the number of columns in the future which start with the word column how do i do such a thing i'm going to take this particular data query and i'm just going to duplicate that query now there are two steps i'm just going to get rid of this step and i'm going to call this query as column list okay so what i'm trying to do is i'm trying to extract all the column names right here then i'm trying to apply some selection criteria to only keep the columns that actually start with the word c-o-l that's what i'm trying to do how do i do such a thing the first job is to get all the names of the columns so i'm just going to use a function called table dot column names and close the brackets towards the end and press enter this is a table and i'm just trying to find the names of the columns as soon as i commit to this function this is going to give me a list of all the columns that i have now i have got all the columns but i want to build a selection criteria that only keep the columns that start with col right so i'm just going to maybe write a function that i'm trying to maybe select a list so list dot select and i'm going to say that hey here is a list this is a nothing but a list in this list why don't you just perform a selection criteria so i'm going to say that the selection criteria is something like this each word or each name of the column should start with call so i'm going to use the function text dot start with starts with and i'm going to say that here is my text which is nothing but every single word and why don't you see that if that actually starts with the word call now i'm just also providing in in case the word call is uppercase or lowercase so i'm just going to write something like ordinal case uh ignore and close that and press enter now if i commit to this ignoring the case sensitivity of call this is going to give me all the names which are starting with the column now in case in the future you have more columns being added which start with the word call you're going to have that added into this query pretty good now this is the part which we are going to use in our function to call all the columns on which we are trying to apply the transformation now let's just get back to the data and start to build our query further all right now that we have everything together let's just put together the broken pieces that we have built so far the column the transformation and put everything together so that it starts to work nicely take a look now the thing that i would want to have is this particular part automated the name of the column the function and the data type to be automated i'm going to get rid of the manual part and start to write the function that we have learned which is nothing but list dot transform i'm just going to come in here and i'm going to start to say list dot transform all right now the first part of the list dot transform is that which list are you trying to transform so i'm just trying to transform this particular list which contains all the names of the columns which are three columns column one column two and column three i'm just going to feed that list right here column list now after that it says hey what transformation would you like to apply in every single column the transformation that i'd like to apply on every single column is take the letters and convert that to a lowercase but the transformation that i'm trying to apply needs to come as a list because you remember that we had a list of a list structure so how do we write such a thing i'm going to say hey why don't you pick up every single item which is nothing but the name of the column and why don't you convert everything into a lowercase so i'm just going to maybe say text dot lower and close that bracket right here and close the outside bracket of list dot transform and close the bracket of table dot transform now if i commit to this you can see that now we have column one column two and column three all the three columns and the letters are converted into lowercase this is awesome now if you take a look at this function this part doesn't really make intuitive sense and let me just help you understand that how is this working if you take a look at our notepad which is where we had the structure defined you had to have an outside list which is a list like this and inside of that you need to have individual lists now to be able to create an individual list i created that particular curly bracket now this particular curly bracket is nothing but this bracket right so in this bracket we need to have two parts the first part is the name of the column and if you take a look at the first underscore right here the first underscore denotes the first name of the column which is picked up from this particular list which contains three items so column one column two and column three so the first item is picked up from here and on the values of that particular column which is nothing but the values of this particular column each and every value is picked up so the second underscore meant for the values of this column so the first value the second value and the third value every single value is picked up and then applied text dot lower on top of that now the only part that you have to kind of you know get your head around is that this particular function needs to come in the form of a list see that you have a list right here and in the list we had three parts name of the column the function and the data type the data type is something that we haven't yet applied and i can do that i'm just going to put in comma here and i'm going to say that this is going to be a type of text and all the three columns are now going to be converted into a text so this becomes a text this becomes a text and this also becomes a text and that completes the three parts that we were trying to chase and now this is going to be good now in case i have another column added right here which contains the letters all of those columns are going to be gathered in this particular list this list is going to just trigger this function and all of the columns are going to be converted into a lowercase automatically all right that's been it i hope you got the explanation and you found this video helpful obviously i used a very very simple transformation to help you understand the logic of applying transformations on multiple columns which is just converting that to a lowercase but should you want to extend the transformations as more complicated transformations you can certainly do that using this particular pattern certainly this was a complicated one if you have any questions around this feel free to drop in a comment and i'm going to be glad to reply in the end a big shout about my attacks and my power query courses in case you're starting out with power bi and dax and power queries seem hard you would want to master the fundamentals first and then you would want to proceed on to solve more challenging more difficult problems of your data i'd highly recommend that you take a look at my courses it is going to be super awesome thanks so much for sticking around and i will catch you guys in the next one cheers and bye [Music] you

Channel: Goodly

Views: 24,137

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

Id: 1fn8fXYw6M4

Length: 14min 5sec (845 seconds)

Published: Sat Aug 20 2022