DAX : Leading zeros in DAX

Introduction

According to Wikipedia : "A leading zero is any 0 digit that comes before the first nonzero digit in a number string in positional notation.For example, James Bond's famous identifier, 007, has two leading zeros". In DAX, there is a difference between handling leading zeros when the datatype is number or it is text. You can solve this by using FORMAT and the REPL function. This is very often used in cases when the type of field of a certain sourcesystem is integer or a field that contains values without leading zeros and one field that comes from another system with leading zeros. Now, you could cast the text column to integer but that is not always possible because of dataquality issues. For instance, there are characters in the column and that is not supposed to be. Then, you have convert the other column to a column with leading zeros.

Leading zero

In case of a number field you can use the FORMAT function, this functions turns a number into a text column in PowerPivot. But you can't use the FORMAT function in case of textcolumn, nothing happens, as shown in the column "FormatonTextDoesNotWork" in the screenshot below.

This is the DAX expression that does not work on text columns:

FormatonTextDoesNotWork:=FORMAT(Table1[Number];"0000000")

So in case of a text column you can use the REPL function. This example is based on a blog of Kasper de Jonge. 

This is the DAX expression that works based on a textcolumn:

=REPT("0";10-LEN(Table1[CodeInText])) & Table1[CodeInText]

Conclusion

This is a small blog about a simple experiment with leading zeros in DAX that can be solved in different ways depending on the type of the column.

Greetz,

Hennie