Dynamic Column Names in Power Query

how your watch on d-pad goodly once again and in this video I'm gonna show you that how can you have dynamic column names in power query let me help you understand what I'm trying to say let's say you have a three column load table we have date we have customer and the amount but I'd like to have dynamic column names that means the user should decide what the column name has to be so I've made a small table right here where I have the existing columns I have taken the column headers from here the column names from here I've pasted them here and this is the new column name that I want so dates should be renamed to invoice date the customer should be renamed to party and the amount should be renamed to value now when the customer changes the names here in the new column the query that gets created should reflect the new column names as the headers now let's just see that how can you do that in power query all right first things first let's just take the data into power query so I'm just gonna click on the table go to data and then I'll click on from table range alright leaders to say that the process of doing the stuff into power query in Excel is going to be the same in power bi if you use power query in power bi as well so the solution remains the same maybe a few things change like loading the data and stuff but I'm sure you can handle that now before I go here and proceed and do the stuff of automatic renaming I just want to explain to you the logic as to how am I going to set up the whole thing before we actually do the stuff so why don't I do a couple of manual steps to help you understand the logic so for now what I'm gonna do is I'm gonna manually change the column header so right click on the column and we have a rename here or I can just maybe double click on the column header and I can just say invoice date the column gets renamed and the second column as well so I'm just gonna call this as party so I've renamed to columns let's just take a look at what is happening so as soon as I did the renaming a step got inserted on the right hand side which is called rename columns and a new formula got written on the top which is stable dot rename columns now if you take a look at the formula the formula actually is accepting two parts it's asking you for a table name that means hey which table should I consider renaming the columns from I'm saying that why don't you go to the table which is change type so in the previous step change type there is a table pick up this table and start renaming the columns and which columns to rename it says hey I have to rename the date column as in States so this is the old column name this is the new column name and then we have customer old column name and the new column name has party but the problem is that these are hard-coded and we have to make them dynamic so we are going to do something with the tables or tree name columns and we are going to make this dynamic but before I make this dynamic you carefully have to take a look at how the formula has been written so if you take a look the entire thing has been there in curly brackets and not just one curly brackets actually two girlie brackets so two curly brackets to begin with and then could two curly brackets to end with and you can see that every column that we are trying to rename is into its individual curly brackets a curly brackets in power query actually means a list and if you don't know what a list means actually means a single columnar data I repeat a list is nothing but a single columnar data so take a look at the first list here which is in two curly brackets and the list has just you know two items the first you know thing is the old column name and the second thing is the new column name this is that means this is the first row of the column the second row of the column again first row of the column old column name and the second row of the column a new column name and you will see that all the individual lists are then combined into a larger list so let's just see that how can you set up that with the existing table that we have so what I'm gonna do is I'm gonna take this table I'm just gonna say close and load and I will load this table to my excel but note that as of now the columns names are not dynamic that means I have manually entered the column name so if I end up changing it here it would not change it here so let's just make a use of this table and try to make the names dynamic so I'm just gonna take this table to power query so I will click on the table again guru data from table range and I'm into power query all right now if you take a look at this table in this table we have two columns that means the existing name is in two column one and the new name is in two column two but hey remember that list means a single column their data since we have three columns of our table we need three lists that means three column their data we need so the first row should have this and the second row should have this the first row should have this the second row should have this as of now these are two different columns so we'd have to transpose this table in order to get you know like a list so I'm just gonna go to the transform tab and transform the table the rules have become two columns the columns have become in two rows and you can see the date and the invoice state is coming to single column and the customer and the party has again come into a single column amount and the value has again come to single column just as the order that we need the old column then you call them the old column in the new column now the only thing which is left is that we have to convert this into a list this is a table as of now which column one column two and column three we have to convert this into a list and then we have to pack all the list into a single list nothing to worry about I'm just gonna do a small step into our query which is gonna do that take a look take a look so I'm going to go to the advanced editor so in the View tab I'll click on the advanced editor and you can see that the last step is transpose table I'm just gonna put a comma here and write another step well let's say create list I'm gonna say table dot two column columns actually you can see that it's asking me for a table and the previous step generated a table I'm just going to pick up that so I'm just gonna say hash and in the inverted commas I'm gonna write transpose table and that's the table that I want to make it as a columnar list so that's the formula table door two column it will take the entire table with multiple columns convert each of the column into a list and combine the entire table into a list you'll take a look once I execute this so create list and this is okay this is okay and in the last part I'm saying hey don't give me the transpose table as the output give me the create list as the output so I'm gonna say create list is going to be the output I'm just gonna say it done and you can see that this has become a list so the entire table has become a list and the individual columns have also become a list so this is also a list this is also a list and this is also let's remember that when we were there here that every single column which has to be renamed needs to have two components the old column the new column the old column and the new column and we have it like that so if you take a look this is the entire list and this has got sub lists so the old column the new column and then again the old column and the new column so on and so forth now that we have this list why don't we plug this list into our data so I'm just gonna come here and instead of writing all of this stuff manually I'm gonna write hey why don't you pick up the headers which is nothing but a list and in that list you have three sub lists that I want to have apart from that I am also going to write another input which is an optional input in table dot rename columns which is in case you have a missing field please ignore that so I'm just gonna say missing field dot ignore right that's what I'm gonna write in case there are the missing fields or any columns that I've left out please ignore them don't worry about them and you can see as soon as I execute the formula the last column which I did not change the name for has been converted into a value column all right now let's just load the data so I'm just gonna say close and load - and you can see the name has been changed to value now let's just take a look so I'm gonna rename this column and say hey why don't you change the name of the customer and start calling it as client and I'm just gonna hit a refresh here and let's just see if the name changes absolutely yes it has changed that's a table with dynamic column names now there are just a few things that you need to remember for this let's just say that I go back to my data table which is where I have done the renaming and I do another step and I maybe I change the type of the data here so let's just say that I come to the value column and I convert that into maybe a number right and you can see that I have converted that into a decimal number a change type step has created after I rename the column and this is where a manual entry has been done once again so the value column is now hard-coded into table tour transform column types now when I say close and load the data and if I try to change the name of the amount column from value to something else let's say value dollars this is what I try to call it as the new column I click here and I say refresh this is going to give me an error because there was a hard-coded step in to power query so if you just take a look here until here this the query is running absolutely fine it has renamed the column all this code but when it goes to the last step because a value column was hard-coded here it did not find that so it gave you an error now what you have to do is you have to remember that the rename columns step has to be done at the end of the query and you simply close and load the data well that's about it if you have any questions on this you haven't understood anything please let me know hit me up in the comments I'll be more than happy to help you out in any way thanks so much for watching this see you in the next one until then take care and bye bye

Channel: Goodly

Views: 44,188

Keywords: Dynamic Column Names in Power Query, Dynamic Column Names in Table, Dynamic Column Names

Id: kwf-E3a3aOE

Length: 9min 24sec (564 seconds)

Published: Mon Dec 09 2019