3 Ways to Add Leading Zeros to Numbers 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 with 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 how you can add leading zeros to numbers.

Table of contents

• 1. Why Add Leading Zeros?
• 2. Add Leading Zeros to Numbers
  • 2.1. Using Concatenate
  • 2.2. Using Text.PadStart
  • 2.3. Using Number.ToText
• Conclusion

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:

= 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 the 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 is sorted 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 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 zeros to your numbers. With the prefixed zeros, your YYYY-MM column sorts 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" }

If this function is new to you, make sure to read in detail about the List.Sort function and the different ways it can handle sorting.

To work towards the leading zeros, the examples work 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 Numbers

2.1. Using Concatenate

The first way to add leading zeros is by concatenating 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 into 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 the default padding character.

In our example, we want to have a “0” used as a 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 the 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 formatted 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" ) 

Conclusion

The methods shown can be useful in certain scenarios. Yet when you have a date column in the same row, you may prefer to use a different method using Date.ToText. This and other hacks you can find in this article about creating a date table in Power Query.

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

Enjoy Power Query!