![]() ![]() We’re also going to remove the trailing comma. The first string function removes the additional spaces at the front and end of our CleanText variable.įor the remaining texts that we have, we’ll also remove the spaces from the result string. In the M code, I nested 2 trim functions. Let’s go back to query to examine the M code. To do this, we can use the trim functions. As you can see from the image above, we have some additional spaces and a trailing comma at the end that we want to get rid of. This no longer returns a list, but it returns some text values. The ‘in’ clause calls the M function Text.Remove and passes the myText variable, and then calls the To.Remove list. Remove created these two lists, and then combined them together into a single list.Īnd then let’s paste in the CleanText variable. Let’s go back to our text cleaning function. Remove.Īnd you see that you can enter a text and then a list of characters that you want to remove from that string. Well, in the next step of my text cleaning function, I used an M function called Text. We now see that the quotes have been added to this list, but why did I create a list? I used the ampersand and the list initializer, then entered those quote signs. Remember that I added an additional list, right? I combined two lists and I used the ampersand to do that. ![]() ![]() It creates a new query and you can see that this list contains all the letters that we see here in the text. Let’s paste in some texts and invoke this function. We see that Text.ToList returns a list of character values from a given text value. You can access the internal function documentation by creating a blank query, entering the function name without the parentheses and by then pressing Enter. Well, I used a function called Txt.ToList. The first list created the quote signs and the second list is a range of characters that I wanted to remove. In fact, it is a combination of two lists. We now see that the ToRemove step creates a list. Then invoke our function, select this merged column, and press Okay. So let’s add a custom column to this query. To see the result of that function, we have to invoke it. So I’m going to copy the ToRemove variable, paste it at the bottom line, and press Okay. If we want to see what the first variable returns, all we have to do is refer to it by name, after the in clause. To prevent that error from happening, I can comment that out for now. Its value should be of type text and the function should return a text value because not all the variables listed below will return a text value.ĭeclaring the return type will result in an error. This function takes one parameter called myText. Our goal here is to clean all the text values in both columns up.įor this task, I created a clean text function.īut first, let’s take a quick look at the M code. You may watch the full video of this tutorial at the bottom of this blog.įor week #2, we have some messy data that needs cleaning up. In this blog post, we’ll take a closer look at a custom function for text cleaning that I created for our ongoing series at Enterprise DNA TV called Problem of the Week. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |