Just before the holidays start, I'd like to share this little trick I came across in Power Query.
I recently had the need to remove zeroes (but it can be any character) from the beginning of a column.
Turns out there's a perfect function for that :-)
The documentation isn't totally clear about this, but for the 2nd parameter (trim) you can add a character you want to have trimmed. The description only mentions whitespace, but you can enter any character in that parameter.
On a side note, I got this nice documentation page inside Power Query by using the #shared function, which I blogged about here.
Text.Trim[Start/End]
And in case you might need it, there's also a Text.Trim and Text.TrimEnd function:
Now onto an example, I created a text column with some values via Enter Data:
I have some combinations of text, numbers, leading and/or trailing zeroes and spaces.
For my 1st example I used Text.TrimStart to remove (only) leading zeroes.
I added a custom column and used the UI to add it like this:
This gives me this nice result:
Only zeroes at the start are trimmed, no spaces or zeroes at other places. Great, exactly what I needed!
I also had a look at the other functions and their workings I mentioned:
Closing
This (hopefully) might not be my last post of the year 😀, but just to be sure:
Merry Christmas 🎄🎅, a great new year 🎆 and happy holidays to you all!
Wow exactly what I was looking for. So simple! thank you for sharing this.
ReplyDeleteThank you! You solved my problem today :)
ReplyDeleteThis usage is not at all obvious from reading the Microsoft doc for Text.TrimEnd, so I asked them to add an example for trimming trailing zeroes.