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

Add leading zero 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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

1 thought on “Add Leading Zeros to a Number in Power BI Using Power Query

Leave a Reply

%d bloggers like this: