Does anyone know how to remove the weird characters that may show up on data uploads?  I'm getting a question mark where a hyphen or apostrophe should exist.  Can a change be made within apptio or does this require a change in the source system?

We have been getting it fixed at the source system, and getting a new feed. May be there is a better way :-)

Nancy,

Perhaps Substitute() function may help.

=Substitute("abc?efg,"?","-")

Hi- I am having same issue with one of my reference data.. 

We get question marks also in uploaded data results if Apptio cannot recognize a character from the source.  We've just been changing the source data.  I'm sure there is a better way but I haven't found it yet!

Correcting the problem in the source system is actually the correct way to go about this. You had the right idea, @Carol St. Claire! Unfortunately, as we discovered, other departments may be less than enthusiastic about changing their systems to make IT's job easier.

Great question, @Nancy Bugajski! I had this issue when we switched to a new ERP system. A bunch of the values were prepended with that question-mark character. After a bit of research, I discovered that this was Apptio's way of showing non-printing characters (NPCs) in the uploaded data. At first, we tried to have them fix the issue in the source system. However, Finance was very...well...they simply refused to even consider it. So, we had to find a solution on our end in Apptio.

Eventually, we settled on using the Edit Existing Column function in the Formulas step of the transform pipeline. The formula that quickly became the most efficient and effective was the ReplaceRegEx() formula. If you know how to form a proper regular expression, then this function works a charm.

For example, we have one formula like this.

Journal Name = ReplaceRegex(Journal Name, "^?(.*)", "$1")

This tells Apptio to replace the value in the Journal Name column with the regex result. The regex formula above isolates all the characters after the first character in a string. The parentheses surround the capture group, which is then referenced by $1 in the next argument. You can have more than one capture group in your regex; just reference the one you want to use in the last argument.

Since your NPC is in the middle of your string, then you could have one capture group before the character and one after the character, then reference both in the last argument of the formula. Something like this (I haven't checked this regex, so you might need to tweak it)...

Column = ReplaceRegex(Column, "^(.*)X(.*)$", "$1$2")

Here, you'll need to replace the X with the specific character you want to remove from the string. I suggest just copying and pasting it into the formula. It tells Apptio to replace the string in the Column column with the concatenation of the first and second capture groups in the regex. Since the NPC is outside the two capture groups, then it will not be included in the concatenation.

Hi all.  I saw this thread, and we have seen this in the Apptio delivery team many times.  It has to do with text encoding, sometimes it is UTF-8 or Unicode, which are pretty standard encodings.  But sometimes text is imported with a more obscure encoding.  There are a couple things you can do.

1) If you are importing a delimited file (like a .csv, or .txt, or a .zip with a CSV inside), Apptio allows you to choose different encodings!  I find if you are getting odd characters, try some of the other encodings; particularly the "windows-1252" or "Windows-1257" and see if those characters disappear or are displayed correctly.

2) If it is a small file or a mapping file that you own, you can fix in the source data prior to load.  I like to open the file in a text editor like Notepad++.  You can select the "show all characters" button and it will even show you whether or not the spaces have been encoded which can be a problem.  In a program like this you can do a find and replace on the special characters.

**this is not an official endorsement of Notepad++ by Apptio.  Any IDE/Text editor will do.

Hopefully this is helpful.

Wow, very helpful, Daniel, thank you for the insight!

I ended up changing my text encoding to windows-1252 and the character disappeared.

This is very helpful. Thank you for sharing your knowledge.

Came across this issue in V11 before. The unusual characters appeared when there was value/data in different format to the rest of the file. For example, some values in a column were in text format while the rest were in numeric, when the dataset format is supposed to be numeric.

To fix it, we open the source file, select the affected column and change the column format to the correct format and saved. Then upload it again. Best still get it fixed at data source level.

You can do a quick check which value was in different format, such as numeric values in Excel would align to the right, while text would align to the left in the column.

Not sure if this applies to R12 as I have not encounter this issue for a while.