How to Handle the Headache of Leading Zeros in Tableau

Data

How to Handle the Headache of Leading Zeros in Tableau

by Stephanie Kennedy
//

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:

Leading Zeros Stripped Example

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:

FormattedEmployeeID

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

Side-by-Side Employee ID Example

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.

More About the Author

Stephanie Kennedy

Analytics Consultant
Do I Need New Running Shoes? Let the Data Decide I gave up getting angry for Lent this year, so I won’t start this blog post ranting about how difficult it is to get my data out of my ...
Off-Label Uses for Measure Names and Measure Values in Tableau Sometimes, I feel like I’m cheating the system when I find a neat way to use something that Tableau has to offer. Other times, I feel ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072