Transform Column Names in Bulk in Power Query

hello everyone this video is going to be helpful for those who want to be able to rename the column names in power query in bulk so perhaps for all of your columns at once with a bit of logic okay stay tuned it's been too many times that i work with customers that have call that work with column names that are just not in the right format perhaps your database administrator likes to have underscores for spaces perhaps there's no spaces at all and there's just a camel case where each of the words starts with a capital and all of the other letters in a word are lowercase but perhaps you want to split all of those kind of things i'm going to show you a few methods in this video on how you can fix this in bulk so that each of those fields and even future fields will be adjusted in an easy way and i will first start showing you one method that's very flexible and to the end of this video i will show you another method which i think you will prefer so stick to the end okay so let's have a look at the screen in the the first the first set that i'm looking here has camel case that means that product key starts with a capital p and has a capital k as well and instead what we would like is a space between these order that key that's the same issue we want to have a space between order date and key and the way to do that is always to have a good look at where the capital letters are and we don't want to do this by hand because if we do this by hand then we're going to have to do this for the key then we're going to have to write order date key and of course while this is really valid and in the top of the screen here you're going to see that it works we're going to have to write this for each of the columns with as a downside that if one of the column names is like changes then your query will break if you get new columns they will be missing in your formula and you will have to adjust and also if some columns will be deleted your query will also break because it will try to rename a column that doesn't exist the method i'm going to show you does not have these issues they will just apply certain logic to all the column names and we're going to try this in different tables okay so let's get started so the first way i want to show you is about renaming columns and as you can see in the top here when you rename something you will always find the old name and the new name within curly brackets altogether and that's the same for the second set that has been renamed right there so if this would continue you'd have all kinds of sets of names that come together that's good to know we are going to make use of this with our renaming function later before we get there i first want to show you something that you will need to transform your columns so what i usually do with the table renaming i make a new step first of all that's now referencing the previous tab called source you can wrap this in a function called table column names and this function needs a table that has columns obviously and will return you a list of all the column names that you have so if i click ok you'll find that these are the column names that were from the step called source in the future if any of the columns are removed for edit this list will simply refresh and either have more items or less or with different names okay the next step is finding out how we can transform this to get spaces in between the words now i'm not going to pretend like i know this by heart so let's let's take the easy way out here if you convert this one to table as the first step then you can then go to add column and there is this ai feature in power query that says column from examples so if you click from selection right there you will find that a new column appears and in this new column you can just write your expected values so in the first part i write product space key then you press enter then for the next one i write order date key with spaces now watch what happens if i click enter now bible query will try and recognize what happens it will try and recognize what logic i mean and then come up with a suggested solution so i'm going to press ok here now and the suggested solution from power query is to have this piece of code and the result of applying that piece of code is all of the column names here and from the looks of it this is exactly what i'm looking for it's a very complex function i would not have cooked that one up myself but hey this is how ai can help you so let's see if we can just make use of this so we press ok perfect so we already have this column available right now and this is really what we need to uh to transform the columns so what i'm gonna do is i'm gonna copy the code that was generated right here or you know what we can make it even easier we can just now right click here and click on drill down and now we have the list with the already transformed column names that we needed okay there is a step left for us so i want to go back to our original query which said source so this one is just back to source so what i did is i made sure that this step here just simply refers back to the source step and that's all we need and the custom step here is already the transformed columns i'll make this a little bit bigger okay so so far the only thing we've done is we made some logic and how we can transform this and now the next step is going to be the magic to actually rename everything so as we saw earlier if i want to rename something then the logic for this is we need different sets for renaming we need the old name and the new name we need the old name and the new name and that for every set that we need now it's very difficult to write this in an easy way however query has a function that can do that for you and that function is called list zip and if we go to this website here then you'll find that list tip i put it right there your list tip can input different combinations of values so if i have abc here and lower letters then i can have a capital abc in the second set of values and the result of putting this in the function is actually these sets next to each other now in our case we can use this by just writing old values new values okay let's see if we can apply that so for our naming what we're going to need instead of this here is we're going to use list zip so our list zip function a list zip first needs a list of items that i want to rename the old layer the old names so here we could say something like table column names and then from the previous step called back to source and this is nothing special yet this will simply generate a list of names that were already there and now as a second argument for list tip we're going to need the transform column names and we already had that that was right here in an earlier step so we could just reference that part called transformed columns okay and then we can close this down and let's see what happens if we do this uh i think we still enlist sydney to wrap this around curly brackets we need the token comma okay i need to have a quick double check so list tip needs this it's a list of lists and a comma so this is a list this is a comma and this one goes out and we close the bracket perfect okay as you can tell it's always good to double check the formatting but the list tip if you look on my website it just requires to to input a list with the curly brackets and if your function already returns a list then it just needs the outside brackets okay so what did this just do for us this actually renamed the columns that we needed so all of these columns right here now have spaces in between the capital letters and nothing here was hard coded okay so this was the the manual way on how you can do that or it's dynamic but it's a little bit more of a broad way let's see if we can do the same for renaming underscores so similarly for renaming underscores we can again say sure table rename table column name we have the source tab there we go and instead of having a separate column we could also use list transform and we can just say that for each of the items in the list each we could say like we could say each text that replace and we want to replace an underscore by a space because this might be fun for your database but in power bi you might want to have a bit more of a friendly code perfect so this little piece of code that we have here actually transformed the list that we needed and we can then just say here transformed columns now we're going to make a new step which is going to be referencing to the source step which is the earlier one and here again we can do the renaming so we're going to call this one like that and again this second part we are going to replace it with the list zip function so we write list zip and we write table dot column names from our previous step or from the source tab it doesn't really matter and then we actually want to have our transformed column names in here so that the old names are replaced by the new names this list zip function i believe it wanted things in brackets here so we close it and we got there and as you can see compared to the previous step all the underscores are now also replaced by spaces very similar and i'm going to show you another way that's very similar we can do something with the prefix as well so you can make this very flexible so if you have a code like this you again start out with table dot column names you write source okay and instead of having just this maybe you just want to have something with a prefix and again you can say i want to have a list transform it will transform all the items here and we can write each and then text combine and we want to combine the value but you know what we can have a yeah we can have this and say like we have like a prefix dot and then we want to combine it with something called which is which is actually each of the values in the list and that is represented by the underscore right here and we close the brackets okay so the function here just took the table column names and just put a prefix before it and all that's left to do is the same as we did earlier so we can reference the source step equals source and it's capital sensitive and then just have a template we can rename this and we adjust what we have in the top very similar to what we had earlier so let's tip it's a little bugging power query that sometimes jumps up and makes the code wrong and here again we say table column names from the source step we're going to put this in curly brackets and then this will be our custom one step which is our transform column names we will just change that around later so this one actually should be called transformed columns and if we then go back to our latest step here then also the renaming here happened with the list zip okay so this is the first method that we can use you can rename your columns based on a transfer let's let's call this a translation list that you actually input in list zip very helpful very efficient a little more complex i'm going to show you another way to do each three of those and i think you're going to be a fan because it's even easier if we go back to that rename camel case then we already know what code we used so we had this complex piece of code here that's what we used to actually transform our columns i'm going to show you how we can make use of that so let's say that we are here now i want to introduce you to something that is another function so previously we used a function called table rename columns so it's set in the top here table rename columns and we then had to input the old list and the new list but there is also a function which is table transform column names table no transform oh table transform column names which also takes the source and i'm just going to show you something fun here because this is going to be very easy if i write something like this just write each then nothing is going to change but if i would now write something like this each prefix dot and then an ampersand and write that part then all of a sudden we already have our prefix here so with a very small piece of code you can make your translation here okay so the camel case had this pretty complex piece of code which said each let and then all of this we got that to the column to take from the column from examples so make use of that okay so copy paste now i can take this part and just put it right here so as a first argument a second argument of the transform column name is part i put this piece of code and there's one thing we need to adjust because this code actually references a column right here and when we're transforming each of these values we don't have a single column so we can actually say remove the column reference and just write an underscore we just need an underscore here to reference each of the underlying values and now with a single step if i go back we have source and on custom one i actually perform the transformation on each of the column types that's pretty awesome isn't it now we can do the same for the underscores so if we go to this part we could for example say we need a new step and then we can just write we're going to translate this and we just need a little bit of a different code so if you want to replace all the underscores we could say each text replace replace each of the values all the underscores and replace it with a space now if i press ok the previous step showed the underscores and this step doesn't very easy now i'm going to still show it for the for the prefix and after that i'm going to also show you how you can conditionally do that so stay tuned so we're going to look at this prefix here and we're going to do something similar and i seem to have left some code here so if i want to change something right there i can again say sure i want to go here and the prefix is prefix dot and then equals the underscore i actually think we just did that let's see so each prefix ah that makes sense i just deleted the first step that's not very good let's see if we control z then we can just have this new step right there exactly so our prefix is also don't like it perfect okay now we're going to make this a little more interesting even because so far we have adjusted all of the columns but perhaps you're telling me that no we don't just want to do this for all the columns we're just going to do it for all the columns that don't have the word date in there is that possible of course that's possible okay so we want to have the word prefix for each of the columns that don't have the description date in there so how do you do that let's take the easy way we can use table transform column names and we can add a condition here so for each of the column names we can say each if text contains and then we can just write an underscore to make sure it checks it for every column if it contains the word date then return the original column so then the underscore and otherwise or else sorry else return the prefix now if we go back to the source you'll find that first these are the original names and if i click on custom you'll find that actually these date keys they have remained the same isn't that isn't that crazy okay so that works great now you can even make this more complex of course because if i write date here and do it lower letters it's not going to recognize it but the text contains function also has a comparer so i could write compare ordinal ignore case and also then it will recognize each of these so this is super flexible this is going to make your solution dynamic you can work together with your database administrator and it's just going to save a lot of time for you and adjusting your phone for your formulas okay so what did you think was any of these solutions new for you do you know a better way did you like it i hope you learned something and if you don't want to miss out on more of these kind of videos i recommend to subscribe click the bell button and with that i hope to see you in the next video thanks [Music]

Channel: BI Gorilla

Views: 21,693

Keywords: Power query, Rename columns, Transform columns, Transform column names, Rename columns in bulk, Mass replace column names, Power query tutorial, List.zip

Id: gndANP-ObQg

Length: 19min 23sec (1163 seconds)

Published: Wed May 25 2022