Excel 2010 PowerPivot: How do I avoid apostrophes being displayed as ’?

Question

When I import a text file to PowerPivot containing apostrophes, they are automatically converted to ’.

I've been manually correcting the few that I find in the PowerPivot window, however when I do this in a column header it causes problems refreshing the data.

A quick Google tells me that ’ often appears in place of apostrophes when there is a text encoding mismatch. It seems that I need to make sure PowerPivot is reading the data as UTF-8.

How can I do this in PowerPivot? I've tried searching online with no luck.

Answer 1

It seems that the default encoding PowerPivot applies to incoming data is ANSI.

This means you need to specify UTF-8 as the character encoding each time you import a new file, or you can edit an Existing Connection by clicking the Edit button from here:

Click the Advanced button from the Table Import Wizard. You can set the encoding here.