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.
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.
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;
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.
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.