Add Leading Zeros to a Number in Power BI Using Power Query

There are a lot of situations where you need to add some leading zeros to a number. A common example of this is to create a month-year combination code using both month and year, and making sure the month is always two digits, regardless of being 1 or 12. There are many different ways of doing that in Power BI. Power Query offers a really simple solution, which I show you in this short article.

Video

Sample input and desired output

The input sample is as below: The month numbers as the number format;

The desired output is like below:

Note that the leading zero is added only to the numbers less than 10 so that the output is always a two-character text. Let’s see how it is possible to do that.

Number.ToText

First, Open Power Query Editor using the Transform Data in Power BI;

Then go to Add Column, and Add a Custom column;

The Custom column expression can use the function Number.ToText as below;

Number.ToText([MonthNumber],"D2")

The [MonthNumber] in the expression above is the title of the column that I want the values of it to have leading zeros. the D2, means I am expecting a two-digit character output. This automatically gives me the outcome. Very simple, and efficient.

If I want to have a 6-digit character output, all I need is to change it to D6;

Format options for Number.ToText

The Number.ToText is a useful function with formatting options, which you can use as below;

• “D” or “d”: (Decimal) Formats the result as integer digits. The precision specifier controls the number of digits in the output.
• “E” or “e”: (Exponential/scientific) Exponential notation. The precision specifier controls the maximum number of decimal digits (default is 6).
• “F” or “f”: (Fixed-point) Integral and decimal digits.
• “G” or “g”: (General) Most compact form of either fixed-point or scientific.
• “N” or “n”: (Number) Integral and decimal digits with group separators and a decimal separator.
• “P” or “p”: (Percent) Number multiplied by 100 and displayed with a percent symbol.
• “R” or “r”: (Round-trip) A text value that can round-trip an identical number. The precision specifier is ignored.
• “X” or “x”: (Hexadecimal) A hexadecimal text value.

Other methods

There are many other methods to achieve the same thing in Power Query, here are a few of those;

• Add a prefix of zero, and then Extract the right 2 characters
• Add a conditional column and checking the length of the characters already, if less than two, then adding a zero
• Using functions such as Text.PadStart to add pading (leading zero) to the text. You need to convert the data type of number to text before that too.
• …

The method of using Number.ToText is my favorite of all, because it is just one simple step and expression. in addition to giving you the options to even have control over decimal and many other formats. such as below;

The N2 means we expect a decimal value with two decimal points as the output. This operation would be a combination of steps if you want to do it using other methods.

Or you can use it to get a percentage format using P;

I hope this short article helps you with a better Power BI solution. Feel free to let me know if you have any questions below.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.