Excel Like TRIM Function in Power Query

in today's video we are talking about spaces and how could you remove spaces between the words in power query now I'm sure you have heard of the trim function in Excel but it doesn't quite work like the trim function in power query so we'll take a look at the nuances and we'll try to remove spaces in between the words in power query and we shall have a lot of fun let's go all right people in power query here and working with some very simple three row data we have some names this is me my son and let's just have some fun together now obviously you can see that there are a bunch of spaces in there there's some uh you know starting spaces some trailing spaces that you cannot see and some spaces in between that we would like to remove now one of the conditions that I'd like to keep is that the name should just have a standard one space but anything more than one space should actually be gone now you'll witness that this cannot be done using the trim function in power query let's just start with that so I'm going to go to the add columns Tab and create a custom column in the custom column I'll start writing a function and the function that I will write is text Dot trim and I'm going to say hey here is the names column in which you will find a couple of texts please trim that I'm going to say okay what I get is that the starting spaces are removed the Trading Spaces if there were any they are also removed but you can see that the spaces in between are still there and I'd like to keep standard once space in between now the trim function and power query is just able to remove the preceding or the trailing spaces but it cannot remove the Extra Spaces within the text what do we do about that now I'm going to go ahead and start to revise my function and I'm going to go ahead and say something like hey I'd like to split this particular text with a space so split it as many number of times as you would find a space so this becomes one word then this becomes a couple of words and then this becomes another word space in another word so on and so forth let's just see how can we do that I'm going to use a function called text Dot split so we have text Dot split and I'm going to say that please use the space as your delimiter once I do that this is going to give me a list and in the list you're going to see that it has actually split uh this into three different words so chandeep is the first one then there was a space here so this got as the second word and the third one is nothing but chambra which is my last name all right now what I would like to do in this particular list that I have received is that I'd like to cancel out in case there were anything more than one space so if you take a look at the next one right here you're going to see that in this list we had a bunch of spaces in there and therefore it has actually split this into a couple of extra columns or extra values in here and I'd like to remove all of that right so how do we do that I'm gonna go in this particular list that we have created and I'm going to say that in this list please do not have any of these blanks now these are not spaces anymore because the space was used as a delimiter these are just blanks and I'd like to just remove the blanks I can use a very simple function called list dot select and I can say that here is a list and in this list I would like to Omit this particular blank selection or this particular blank value and I'm going to say hey in each underscore should not be equal to a blank right it's not null it's a blank right so I'm going to say here is a list and in each item of this list it should not be equal to a blank that's all I'm going to to say okay and let's just take a look at what's the result of this list now this results gives me the three texts that I have but if I combine the texts back I should then add one space which was my default requirement so I'm just going to go back in this function that I'm writing and I'm going to say that I'd like to now combine it back but now this time I'd like to have one standard space back in there so I'm gonna say that text Dot combine asks you for a list so the first part is definitely a list and the second part is your separator and my separator in this case is going to be a space beautiful I'm going to close in the brackets and press OK what I get is the text back but this time there are no trailing spaces there are no preceding spaces and obviously we have one standard space everywhere this is pretty awesome now as of now we have been able to solve the problem but our solution is not really scalable what if this was supposed to be done in a couple of queries that you have in your query right here I mean in your data model and what if this was supposed to be done on multiple columns that how do you scale the work that you have done here we're going to do that using a function let's go with the function all right people if you've never worked with functions before what functions allow you to do is they allow you to pack custom logic and combine multiple different formulas together to create a unique function of your own that can clean the data in a unique way and make it more scalable to clean that so that you don't have to write the code over and over again now you will see that once we take a look at on this example now I have a notepad here this notepad contains the same M code that I wrote in the custom formula up on the top it's just that it's a bit formatted that's why it might seem a little different but it's the same code that we just wrote now if you take a look at this particular M code right here in this m code we asked the user to give us some couple of strings that we would want to clean if you remember the trim function that I wrote just a while ago in the trim functions I imported the name of the column which I wanted to clean which contained three strings that were eventually cleaned up similarly when I'm creating this as a function and I don't know what is my input going to be I'm going to ask the user give me some texts to clean but how do I ask the user right I'm going to create a variable for that so let's just start to create this into a function I'm going to go ahead and start to declare a variable and the variable name could be anything so I'm going to say something like input text as a text now let me just tell you what exactly is happening here the input text is the name of the variable that I just declared and you can declare any name that you feel like here I am saying that hey user once you're giving me the input text please provide me that text in the form of a text data type it cannot be a number because all the functions that you're using here are all going to work with only texts now once you have written that you can just maybe write the rocket sign right here anything that succeeds uh the rocket symbol is going to convert it into a function right all right now I'm going to start to write my let statement so I'm just going to say let and just call this as something like clean data that's the name of the step and that's the only step that you would need right here and we are pretty good to go now I'm just going to move this up now in the clean data step I'm going to start to clean the data but I would not start to clean the names column because I don't know what the user is going to give it to me to clean so I would say that this formula that I have written right here this formula all of this is going to work on the user's input and the user's input is going to be captured in this input text variable that they use is going to give it to me so I'm just going to maybe replace that and start to write something like input text all right pretty good and I'm just going to maybe write the in statement and finally close the loop and say that this is going to be the clean data as an output so just one single step and I do all of the same function work I declare a variable on the top which is where the user is going to declare you know some texts that he would want to clean I captured that text within my function and then I input that output right here all right pretty good now why do we use this m code that we have written we have to create a new query so I'm just going to copy this m code right here I'm going to go in the queries right here and I'm going to create a new and a blank query in the blank query I'll move over to the advanced editor and I'll delete everything and I'll paste my code right there let's just delete this and kind of clean the code up all right pretty good and I'm just going to click on done and what we get is a function right here you can see that the icon has also changed now the name is bad I can just rename this of course my my function as it moves spaces unless you start to use that particular function I'm going to go back to table number one and I'll just delete this custom column that I just created I'm just going to get rid of that and I'll now start to use a remove spaces function which is where all of the logic that I just built a while ago I could use that with a single formula called remove spaces how do I do that I'm going to go to the add columns Tab and I'm going to maybe either create a custom column or I can invoke a custom function so I'm just going to maybe for now create a custom column and I just find it easy and I'm going to say that hey I would like to call a function that I have created the name of that function is remove spaces and I can just maybe have that and I'm going to say that this is going to ask me an input text and what is that input text that is going to be nothing but all the names that you have in the names column I'm going to close the bracket and press OK and what you get is cleaned text and all that you have done is written a very simple function this is pretty awesome I'm not done yet because there is still room for scalable ability if you take a look at the current query that we have been able to make we are at the moment using a function cleaning just one particular column so we reference the name column right here into our function and we clean that up that's pretty easy but what if we wanted to clean all the columns off the table or maybe multiple Columns of the table all in one go using the function that we have created let me just show you a very interesting trick so I'm going to get rid of this particular step and I'm going to duplicate this column so I'm just going to maybe uh say come right here and I'd say hey I would like to duplicate the column and I'd like to now clean both of these columns how do we do that I'm going to maybe create a new step and I'm going to start to write a function called table dot transform columns if you don't already know this function I suggest that you take a look at the video that I have done explaining this function in Greater detail in the past but for now just take a look so I'm going to say something like hey I'm trying to transform all The Columns of this particular table and on all the columns of this table the function that I'm trying to use is remove space is the function that I have made how do I do that I'm going to start to write something like table dot transform columns once I do that it is asking me hey what's the table in which you're trying to work with so I'm trying to work with duplicated column which is nothing but the previous step which is nothing but my table which contains two columns now in this table I would like to work with all the columns therefore I'll leave the list of the columns as blank that means I'm not particularly trying to choose one or two columns I just want all the columns to be transformed all right I do that and I put a comma and says hey what's your function that you're trying to run so the function that I'm trying to run is nothing but remove spaces I do that and voila what do we have all the columns now now clean using the function that we have created using to move spaces that is awesome all right that's been it let me know if you have any questions around this and I'll be glad to reply please put down your questions in the comment section we obviously started out simple and we wanted to clean out our or text and remove the unwanted spaces we then learned how do you create a function and then we tried to make our function more scalable and try to apply that function to All The Columns of the table I'm sure some parts of the query or maybe the entire video that I have done discussing multiple tricks might have been useful to you in some or the other way possible to create more Dynamic and more scalable queries of your own work all right let me know if you have any questions around this in the end a big shout about my tax and my power query training courses in case you are a beginner and you'd like to understand the fundamentals first and then move on to solving more difficult more challenging problems even of your own data I'd highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking around all this while and I will catch you guys in the next one cheers [Music] thank you

Channel: Goodly

Views: 18,564

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

Id: Y_pnQXHcv4k

Length: 11min 55sec (715 seconds)

Published: Fri Nov 04 2022