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:
Continue reading “Quick Tips: Adding Leading Zero to Integer Values (Padding) with DAX and Power Query”