This question comes up in training almost every time:  

“My data has leading zeros. When I connect to my datasource in Tableau, it strips them off.  How can I get them back?”

How, indeed. This solution actually came from a training attendee, which pretty much adds new meaning to the phrase “train the trainer.” It works beautifully, and because Tableau allows you to save extracts out, anyone in your company that uses the data you’ve formatted will be able to partake of your greatness.

First, let’s show an example of what we mean:

What the user wants here is for the EmployeeID to be formatted as a six-character field, with leading zeros for values less than six characters long. To wit:

876543

007521

000005

093716

000078

000290

That’s not too much to ask, is it? Well ask and ye shall receive.

Get Those Zeros Back Where You Want Them

First, create a calculated field by right-clicking anywhere in the Data window in your worksheet. Then, enter this calculation:

Right(“000000” + Str([EmployeeID]),6)

Where:

• “EmployeeID” is the name of your field.
• The number of zeros in the first section is equal to the length of your field, including the leading zeros.  
• The number of zeros in your ‘Right’ function is the same as the number of zeros in the first part of the calculation. In this example, the field length we need, including leading zeros, is 6.

Here is what that looks like in the Calculation box in Tableau:

Here is a side-by-side comparison of the original EmployeeID and the newly formatted one in a Tableau worksheet:

The absolute beauty of this trick is that it will work for any size field you need. All you have to do is change the number of zeros and the numerical value in the ‘Right’ function.

I can only assume that using leading zeros is a vestige of the olden days, back when fields all needed to be a fixed-length because database tools were not as sophisticated. It matters not how we got here, it just matters that nearly every corporation has this problem in some form or another. This is a simple and effective way to handle it, using Tableau to make your data shine!

View the results in Tableau by downloading the attached workbook below.