Set the Data Type of a Column
When you paste or link to data, PowerPivot will automatically detect and apply data types. After you have added the data to the workbook, you can manually modify the data type of a column to change how data is stored. However, changing the data type might impact data precision or result in data loss. If you just want to change the format of how the data is displayed without changing the way it is stored, you can do that instead.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To change the data type or display format for a column |
|
Considerations when Changing Data Types
Sometimes when you try to change the data type of a column or select a data conversion, one of the following errors might occur:
Failed to change data type
Failed to change column data type
These errors might occur even if the data type is available as an option in the Data Type dropdown list. This section explains the cause of these errors and how you can correct them.
Understanding the Current Data Type
When you add data to a PowerPivot workbook, PowerPivot for Excel checks the columns of data to see what data types each column contains. If the data in that column is consistent, is assigns the most precise data type to the column.
However, if you add data from Excel or another source that does not enforce the use of a single data type within each column, PowerPivot will assign a data type that accommodates all values within the column. Therefore, if a column contains numbers of different types, such as integers, long numbers, and currency, PowerPivot will use a decimal data type. Alternatively, if a column mixes numbers and text, PowerPivot will use the text data type. PowerPivot does not provide a data type similar to the General data type available in Excel.
Therefore, if a column contains both numbers and text values, you will not be able to convert the column to a numeric data type.
The following data types are available in PowerPivot workbooks:
PowerPivot data types |
|
|
|
|
|
|
If you find that your data has a wrong data type, or at least a different one than you wanted, you have several options:
You can re-import the data. To do this, open the existing connection to the data source and re-import the column. Depending on the data source type, you might be able to apply a filter during import to remove problem values.
You can create a DAX formula in a calculated column to create a new value of the desired data type. For example, the TRUNC function can be used to change a decimal number to a whole integer, or you can combine information functions and logical functions to test and convert values.
Understanding Data Conversion
If an error occurs when you select a data conversion option, it might be that the current data type of the column does not support the selected conversion. Not all conversions are allowed for all data types. For example, you can only change a column to a Boolean data type if the current data type of the column is either a number (whole or decimal) or text. Therefore, you must choose an appropriate data type for the data in the column.
After you choose an appropriate data type, PowerPivot will warn you about possible changes to your data, such as loss of precision, or truncation. Click OK to accept and change your data to the new data type.
If the data type is supported, but PowerPivot finds values that are not supported within the new data type, you will get another error, and will need to correct the data values before proceeding.
For detailed information about the data types used in PowerPivot workbooks, how they are implicitly converted, and how different data types are used in formulas, see Data Types Supported in PowerPivot Workbooks.