How to Add Leading Zero in Power Query while Maintaining Specific Number of Digits ?

Having leading zeros is crucial when a standard data length is necessary.

Here is a sample Calendar table featuring columns for Date, Year, and Month.

The task is to prepend a zero to single-digit numbers while keeping two-digit numbers unchanged, so that 1 becomes 01, while 12 stays as 12 since it already has two digits.

Here is my solution:

Click on Custom Column at the Add Column tab.

Using the M code below, replace the Month with your own column.

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

Note that, If column data type is a Text data type, you can remove “Text.From” code. Text.From converts the Month into text data type before adding leading zero because Text.PadStart only works in text data type.

Click on OK to add this custom column.

Here the custom column, New Month is created with a leading zero while maintaining 2 digits.

Hope you find this simple article helpful. Like and follow me to stay tune for more tutorials regarding Excel, Power Query, Power BI and so on.

Wishing you a fantastic day ahead and enjoy your analysis!