Using M to dynamically change column names in Power Query

hi everyone in this video we're going to explain how to create a dynamic column changing function we will use lists we'll use records function invoke and it will explain each syntax so this will be a pretty educational video [Music] now the issue that my client had was that he was exporting files from its system and on each export we had a randomly added spaces before the column names so all the other structures were intact and were correct but for some unknown reason the program exported column names with a random number of spaces before the actual name of the column and since we were doing the we were going to do the export for every single day and that export will continue to go indefinitely we had an issue because we couldn't assume which column we will be exported with how many extra spaces so we had to find a way to cleverly clean the column names so no matter which column name we receive we will clean it from extra spaces we will clean from extra non-printable characters and we even decided to uppercase every single column so for that reason we developed this function and we will explain everything with this video the whole procedure of creating it so first let's go to get this table inside of our query we will explain it on the extract from contoso database with where i intentionally added some extra spaces uh before the column names so let's the first step is to get this data in the power query so we will select the table and we'll choose from table range and this is this will load the power query editor and first let's call this raw input row in input we need to somehow get column names changed so first let's try to do it manually to see what is the code behind it so if we go to manufacturer it has some extra spaces and we remove it product name also and let's see another one i think promotion name also yeah so if we do it manually we will receive an additional step called rename colors and this is a function invoke so this is a table rename columns function which is accepting two arguments the first argument is the previous step the table as it was in the previous step of iteration and this is the syntax we are interested in so if we copy this syntax and we paste it as a blank expression so let's go to blank query equals and we paste it as a blank expression we will receive a list of nested lists and each of these nested lists is actually holding the old or the dirty column name and the new replaced cleaned one so in order to get this function to create a dynamic type of changing column names we need to somehow introduce a dynamic list of lists expression so we need to create this expression dynamically and that is what we are going to do in the following part of the video so we know that each of these lists is holding the previous and the new column name so we need to get first step we need to get the column names the easiest way to get the column name from the table is to demote headers or use headers as first row option and then always remove this change type and then we so we know that we need to access the first row of the table so we can use the record invoke syntax and to create a record from a table we need to use curly brackets option and we need to enter the row index remember in power query index starts from 0 so if we want to access the first row in this table we need to add a 0 as an index after we confirm we will receive a record type object as we can see here in the this icon and it is holding the first row in the table so the first row of the demoted table next thing we need to do we need to convert it into a table and now let's duplicate this column so this is the column holding the nasty dirty data and this is the column in which we will do all those cleaning and transforming the data the column names to a proper form so let's go right click transform clean right click transform oh no not length but transform trim and let's do transform uppercase and just so that you know what we are trying to achieve let's rename this column to old name and this one to a new name new oh come on new name so we have two colors ignore this one and in the left part in the left column we have the old dirty name and on the right part we have nicely cleaned name but at the moment as we can see these are not lists these are only values inside of a table but we need to get them in the shape of a nested list so how to do that the easiest way is to go to add column add a custom column let's call this nestet lists and now we need to push the old name and the new name columns into a syntax that is going to form a list out of them so if we add these curly brackets and the column name of the old and the new column and we close it again with the curly bracket we will receive a list object so for every row we'll receive a list object that is holding if we observe down below that is holding the dirty and the cleaned type of column name if we observe the formula bar we can see the each keyword and each keyword is the key to creating such nested list table at column is a iterative function that is accepting three three arguments the first argument is the name of the table or the table as it was in the previous iteration then the nested list is the name of the current column or the additional column edit column and each syntax this each keyword means that the same thing the same calculation on the same transformation will occur for every row in this table so for each row in this table we will create a list as introduced with this curly bracket syntax that will accept the old name so that will push the old name of the current row iteration and the new name of the current iteration into the nested list so far so good so we created a nested list but we still need to create a list out of those nested lists and the easiest way to do that is by accessing a column that is holding that list and transforming that column to a list how to do that we simply write in square brackets the name of the column that we wish to drill into so we enter the name of the column in square brackets and we will drill into that column which is holding the list and now we receive the lists of lists an object which is list and it's holding a nested list just to give you an idea of uh how to access records and lists from a table i would just do a small regression so let's load this table it has only three columns it's pretty simple from table range and if we want to let's add another step so if we want to access a row from this table we need to use bracket syntax so curly brackets if we want to access the first row we need to add index 0 and that will create a record type object with with the first row of the table in case we want to access a list if in case we want to access only a single column and create a list of that column then we need to use the square bracket syntax and we need to write the name of the column so writing the name of the column will get get us to to that to the values of that column transformed into a list and how to access only a single value from the list so for instance if we want to access this number eight we would need to call both syntaxes simultaneously so we will need to add index number one and we will drill into number eight the same thing the same concept is applied when you do right click and drill down to that particular value also if we observe on the top we can choose to change the order of record and list invoke it does not matter so both are acceptable so now after we explained it in simpler terms how to access rows and columns from a table let's head back to our uh let's head back to our script so far we created an expression the second expression of the table rename columns function so now let's just we need to bring back the raw table so let's go again let's put it back inside of power query okay and now let's change this raw input into fix clean so this will be our function later on we have the most important part so we have the part that will change the column names but we still need to invoke that part so we need to invoke that expression over the table that will going that is going to enter our function so we need to change so we need to change this query into a function this query is going to accept only a single table input and that will be a dirty dirty table input and we need we want it to be a type of table and we want the result of this function to also return table and now this is the syntax to create function the easiest way to preserve oh i see i've made some mistake here so these two steps should never definitely not enter our function because they are hard coded column names you should always remove any hard-coded column names in your function because they are most of the time they are the issue they are the ones that will break your query so anytime you have hard-coded names like change column time step or rename columns those steps you want to somehow surpass or not to not include in your query script or function if they are not uh strictly needed okay we need to since we removed those two steps we need to make slight adjustments to the code so squad and the easiest way let's get back to our function the easiest way to preserve this query syntax is to link it to a variable and let's call this variable a list of lists and let's say that this variable or this applied step is holding all this logic now since this will be an inner variable or the or the variable of the inner environment we need to add another let to introduce the outer environment and the outer environment will hold this step and will also hold additional step that will be cleaned column column names and this step will be an easy one we will call a table dot rename columns i hate when this happens and the first argument which is the dirty table will be the input table of the function so this is the first argument and the second argument will be a list of lists so here we will provide a list of all the dirty names and all the claim names that we previously created dynamically and of course we need to exit our outer variable outer environment so we need another in and this will also be the exit step of of the outer environment as we can see we use oh there's one more thing that we need to do this dirty table has to be also the input table of the inner environment it is possible so this is the outer environment variable and it is possible to call the outer environment variable from within the inner environment but the opposite does not apply so you can't call for instance when you are in the outer variable clean column names is an outer left variable and you can't call an inner applied step so for example trim text you can't call it from within the outer environment so only the outer to inner applies the inner to outer does not okay let's click on done and we should receive our dirty table function and if we were to invoke this function over our dirty table we should receive our freshly cleaned table with all the column names properly formatted now in case you wish to further adjust the query or the function to for instance accept additional transformations of the new column names or you wish to filter out to only to only change certain column names you can do all of that in this step this step so after after the uppercase text here you can add additional logic in case you wish to filter columns or you wish to do some other transformations of the new column name you should always leave the old column names as they were because in case there will be mismatch you will receive a nerf and with this function you can clean pretty much any column name structure to the one that you wish and you can do it dynamically so every table that goes inside as an input table for this function will get transformed and all the steps will be applied so every dirty table will get a new formatted nicely looking column names so i hope you find this video entertaining and insightful if you have any questions please post it down below and if you like this video please hit like and subscribe button and we will see you in the next video bye

Channel: Exceed Learning

Views: 11,807

Keywords: powerquery, etl, powerbi, excel, function, data

Id: jsP8BcgJZhA

Length: 18min 45sec (1125 seconds)

Published: Wed Oct 14 2020