Power Query Trick: Remove (Only) Leading/Trailing Zeroes From Text

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:

In case you want to test it yourself, I put the M code I used in my GitHub.

Closing

I hope you found this one useful, let me know if you already use this or are going to in the future!

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!