Quick Tips: Adding Leading Zero to Integer Values (Padding) with DAX and Power Query

There are some cases that we want to add a leading zero to a digit, such as showing 01 instead of 1, 02 instead of 2 and so on. We have two options to do this in Power BI, doing it in Power Query or doing it with DAX.

Adding a Leading Zero in Power Query

The first method is doing it in Power Query using the Text.PadStart() function.

Here is how the syntax of the function:

Text.PadStart(text as nullable text, count as number, optional character as nullable text)

And here is how the function works:

Text.PadStart(input string, the length of the string, an optional character to be added to the beginning of the string util we reach to the string length)

For example, Text.PadStart("12345", 10 , "a") returns aaaaa12345 and Text.PadStart("1", 2 , "0") returns 01.

Let’s create a list of integer values between 1 to 20 with the following expression:

{1..20}

Now we convert the list to a table by clicking the To Table button from the Transform tab:

Now we add a new column by clicking the Custom Column from the Add Column tab from the ribbon bar:

Now we use the following expression in the Custom Column window to pad the numbers with a leading zero:

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

Here are the results:

And the last step is to correct the columns’ data types by selecting all columns (press CTRL + A) then clicking the Detect Data Type button from the Transform tab from the ribbon.

At last we click Close & Apply to load the data into the data model.

Adding a Leading Zero with DAX

I am a big fan of taking care of any sort of transformation activities in Power Query. But, in some cases, we want to add a leading zero to a number just to format the number. I mean, adding a leading zero to numbers is not necessarily a transformation activity. You may want to pad the results of a measure with a leading zero if the number is between 0 and 10. The following method works regardless though. And… it is very simple. Simpler than you think. We just need to use the FORMAT() function in DAX. The output of the function is a string.

The syntax of the FORMAT functions is:

FORMAT(<value>, <format_string>)

And here is how the function works:

FORMAT(a single value or an expression that returns a single value, a format string)

The formatting template of the function is where all the magic happens. There is a wide range of formatting templates including predefined ones and custom formatting.

Here is how we pad a leading zero with DAX:

FORMAT(<a numeric value>, "0#")

We just need to use the above pattern in our calculations either in the calculated columns or measures. In our example, we add a calculated column, so here is the DAX expression for the calculated column:

Number with Leading Zero in DAX = FORMAT('Leading Zero'[Number], "0#")

That’s it.

But wait, what if our list of numbers starting from 0? Let’s change our sample data in Power Query so the list starts from 0, and load the data into the model again. Here is what we get:

Hmm! That doesn’t look nice!

Here is the solution in Power Query:

if [Number] = 0 
   then "0"
   else Text.PadStart(Text.From([Number]), 2, "0")

You may think that we can use the same logic in DAX using IF() function, which we definitely can, but wait; I want to show you a better trick. Here is the DAX expression without using IF():

Number with Leading Zero in DAX = FORMAT('Leading Zero'[Number], "0#;;0")

Each format string can have up to 4 sections. We can separate each formatting section using a semicolon (;). If the format string has one section then it applies to all values, otherwise:

• The first section applies to positive values
• The second section applies to negative values
• The third section applies to zeros
• The forth section applies to Null values

So, the format string of the latter DAX expression ("0#;;0") add a leading zero to each integer value, but if the value is zero, then it shows zero.

If you want to learn more about Data Modelling with Power BI, make sure to get your copy of my book, Expert Data Modeling with Power BI which is available on multiple platforms.

There is another scenario that may not even require adding a new calculated column with padded values. Suppose you have a table with an Index column, just like what I have in the above example and I just want to show the padded values. In that case, I don’t even need to add a calculated column. Indeed, I can format the Number column to show the padded integer values. Let’s see how it is possible:

• Select the Number column
• Use the following formatting string in the Format dropdown of the Formatting section from the Column tools from the ribbon

"0#;;0"

This is very cool, when we format values, we are not changing the data type. So after formatting the values, they are still numeric values, which in my example it is Whole Number.

Easy!

Bonus Item

This bonus is for those who read this article through the end. Did you know that you can convert integer date values to Date using the FORMAT() function such as converting 20210910 to 10/09/2021?

Here it is:

DATEVALUE(FORMAT(20210910, "0000/00/00"))

You can download the PBIX file from here.

Enjoy!