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

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}
Creating a List of Integer Values Between 1 to 20 In Power Query
Creating a List of Integer Values Between 1 to 20 In Power Query

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

Converting a List to a Table in Power Query
Converting a List to a Table in Power Query

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

Adding a New Column to a Table in Power Query
Adding a New Custom Column to a Table in Power Query

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

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