3 Ways to Add Leading Zero to Number in Power Query

A leading zero is any 0 you add before the first nonzero digit of a string. By definition, these are invisible when a value has the type number, so a text value is required. Adding leading zeros to numbers in Power Query is a useful skill to have in several cases. When your value requires a fixed length, leading zeros can provide you a foolproof way to achieve that. Leading zeros also help when sorting text values that include numbers. This can be helpful since text that includes numbers often sorts in an odd way. In this post I show you three ways on how to add leading zeros to numbers.

1. Why Add Leading Zeros?

Let’s have a look at when leading zeros are useful. Have a look at what happens when sorting the following list. The desired sort order for number values presents no challenges:

Sorting Numbers:

= List.Sort( { 1, 11, 111, 20, 35 } ) 

  Result =   { 1, 11, 20, 35, 111 } 

As you can see the result sorts from low to high in the expected way. Things get more challenging when you sort text values that include numbers. Examine the following examples:

Sorting text incl numbers:

= List.Sort( { "a1", "a11", "a20", "a111", "a35" } )

  Result =   { "a1", "a11", "a111", "a20", "a35" }

  Desired =  { "a1", "a11", "a20", "a35", "a111" }
Sorting text YYYY-M:

= List.Sort( { "2021-1", "2021-9", "2021-10", "2021-11" } )

  Result =   { "2021-1", "2021-10", "2021-11", "2021-9" }

  Desired =  { "2021-1", "2021-9", "2021-10", "2021-11" }

In previous two examples, the List.Sort function does exactly as you instruct, sort alphabetically that is. Alphabetically, 111 comes before 20, and 11 before 2. After all, in alphabetical sorting numbers come after text. And each number sorts individually. That’s why the numbers don’t show chronologically anymore.

Yet you may want to sort by the text part of your value first, and to respect the number value that comes after. So what is a good solution to be able to sort text values but get the numbers in the desired order? You can add leading zero’s to your numbers. With the prefixed zeros, your YYYY-MM column sort nicely again.

Sorting text YYYY-MM:

= List.Sort( { "2021-01", "2021-09", "2021-10", "2021-11" } )

  Result =   { "2021-01", "2021-09", "2021-10", "2021-11" }

We will start with the following data:

The goal is to transform the numbers in such a way that the result shows YYYY-MM, so for example 2005-01.

2. Add Leading Zeros to Number

2.1. Using Concatenate

The first way to add leading zeros is by concatening text values. Currently, both columns are formatted as whole number. To concatenate data, those need to be in text format. Step 1 is to format the month numbers with 2 digits each. In a custom column you can write:

= "0" & Text.From ( [Month] )

This adds a 0 before each value, and turns it to text:

As the picture shows, the numbers 10-12 also have a 0 added to the start. The next step is to extract the last two characters of each text value by using the Text.End function. This function takes as arguments:

  1. A text value
  2. The number of characters to extract

You can write:

= Text.End( "0" & Text.From ( [Month] ), 2 )

What’s left is to add the year to this formula. The final formula would then be:

= Text.From( [Year] ) & "-" & Text.End( "0" & Text.From ( 
[Month] ), 2 )

The result is a column with a format of YYYY-MM like below:

2.2. Using Text.PadStart

Another way to reach the same result is by using the Text.Padstart function. This function adds characters to the start of a text value. The arguments for writing it are:

  1. The text value to add characters to
  2. The amount of characters the transformed text value should have
  3. An optional character to add at the front of the text

Argument 3 is optional. When it is left out, a space is used as default padding character.

In our example, we want to have a “0” used as padding character. All values for the Month column should have a length of 2 characters. We try this by:

= Text.PadStart( [Month], 2, "0" )

Power Query will throw the error: “Expression.Error: We cannot convert the value 1 to type Text.”

The function requires a text value, whereas the Month column has the number type. So in fact we are adding leading zeros to text. To fix this you can change the formula to:

= Text.PadStart( Text.From( [Month] ), 2, "0" )

The only thing left to do is to add the year to the formula to get to the desired end result:

= Text.From( [Year] )  & "-" & Text.PadStart( Text.From( 
[Month] ), 2, "0" )

2.3. Using Number.ToText

A third and easy way is to use the Number.ToText function. This function formats a numeric value to a text value with the format you specify. You could for example format a number as a percentage, give it a scientific notation ( “4.0000000e+000”) but also add leading 0 to numbers.

The syntax for the function is:

  1. a number value
  2. optionally a format input as text
  3. optionally the culture of the value as text

In our case this means that the following code can format the month numbers with a leading zero:

=  Number.ToText( [Month], "00" ) )

Just like previous examples, we then add the year number to get to the final formula:

=  Text.From( [Year] ) & "-" & Number.ToText( [Month], "00" ) )

And that rounds up three different ways on how to add leading zeros to integers. Which is your favorite way?

Enjoy Power Query!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment