This isn't a count distinct problem - it's a JET engine problem. The tool used to connect to text files or Excel files is assuming the orders are numbers. By the time it's in Tableau, it's too late to get those back. You need to either create a schema.ini (if your source is a text file), or add a dummy line of data at row 1 where the order number is "a1" or something like that. If this is Excel, formatting the column as text should also work.
See the idea how on how Tableau should be warning you about this: http://community.tableau.com/thread/110812
Here is a link to a knowledge base article that describes some options for getting the JET engine to correctly guess types. Alex mentioned the schema.ini file. There's also a registry setting that determines how many records the JET engine will read to make its guess.
A few questions: What is your data source? Have you tried an extract and does that make a difference? When you view the underlying data from Tableau, do you see 123456, 123456, 234567 and then all NULL?
Hi Joshua - my data source was an excel file with a xlsx file extension. The column of data in question was a field called order number as outlined in my original post.
I fixed the problem by changing the connection type from LIVE to EXTRACT. Now my countd function works correctly. It was not even an option with a LIVE connection type.
This stuff about JET and schema.ini files is just way over my head. I have changed all of my connection types to EXTRACT and cannot detect any disadvantage.