When you add data to a Data Model, Excel automatically detects and applies data types. If you’re using the Power Pivot add-in, you can change the data type if it was assigned incorrectly. The most common reason for changing data types is when numeric data is imported as strings, preventing aggregations and other calculations from working as expected.
-
In the Power Pivot window, select the column for which you want to change the data type.
-
On the Home tab, in the Formatting group, choose a data type from the Data Type list.
-
Text
-
Decimal Number
-
Whole Number
-
Currency
-
TRUE/FALSE
-
Columns that contain both numbers and text values cannot be converted to a numeric data type. If you need to modify data values in order to use the data type you want, you’ll need to edit the values in a source file and then re-import the column.
Considerations when changing data types
Sometimes when you try to change the data type of a column or select a data conversion, 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 Data Model, Excel checks the columns of data to see what data types each column contains. If the data in that column is consistent, it 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, Excel assigns 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, Excel applies a decimal data type. Alternatively, if a column mixes numbers and text, Excel assigns the text data type.
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. Filtering during import requires that you import using the Power Pivot add-in.
-
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 when changing data types in Power Pivot
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, Power Pivot 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 Power Pivot 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 a Data Model, how they are implicitly converted, and how different data types are used in formulas, see Data Types Supported in Data Models.
