Re: Count of distinct serial numbers, some with extensions
Alternatively, you could choose to create an extra field in the datamodel, since this field apparently not really is the serial but has some extra information attached to it.
Create something like "left([Serial Number], 9) as SerialNumber" in the table in the load script and count distinct that instead.
If the extra information has value for your application you could store it in a separate field. You can than even decide to leave out the "EXPLODED" if all of them have that and leave the remaining value as a time stamp or turn the "EXPLODED" into a separate flag field if it can be multiple options and has added value.