﻿ 3 Ways to Add Leading Zeros to Numbers in Power Query - BI Gorilla
Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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.

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?

```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 for 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.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:

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" )
```

The methods shown can be useful in certain scenario’s. 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 favorite way?

Enjoy Power Query!

Share on:

Power Query

Power Query

Power Query

Power Query

Power BI

Power BI

## Best Ways to Share Reports in Power BI

1. • This should work:

2. Hey Tom,

The formula should work. The screenshot shows the exact code and result of the step. You can use:

```=  Text.From( [Year] ) & "-" & Number.ToText( [Month], "00" )
```
3. Hello Rick,
your option “2.3. Using Number.ToText” doesn’t work for me. I did totally same as you and no success… Does it really work?

thanks to check and reply, Tomas

• • 