I’ve attempted uploading a csv file with the correct datatypes but once uploaded it still shows certain columns as text. Is there a way or formula to transform a text column into a numeric value?
This creates a new column but with ‘ERROR’ down the column.
Hi,
A couple of things, you have “[ Payment Amounts ]”, the square brackets indicate you are expecting the field to have spaces before and after the “Payment Amounts” field name, this is not common, should it be [Payment Amounts] instead?
When you click “Preview rows”, does the data return with your new expression content?
If it does show values then the “error” is actually a warning. You need to look at the property or dimension this value is assigned to (i.e. where you dragged and dropped it), if you look at the properties of this you may have it set as Text which is causing the issue rather than your code:
If all else fails add another expression to find out if you have any “funnys” with the data:
CASE WHEN TRY_CAST([Payment Amounts] AS INT) IS NULL
THEN ‘Cast failed’
ELSE ‘Cast succeeded’
END
The above code will try to convert the column, if you get fails then you know the base data is not correct and you need to ammend.
Regards,
Jon
These are warnings rather than errors. The building of your database will automatically convert between datatypes, however this conversion can impact the build time and hence the warning.
In your case, the import from Excel incorrectly datatyped the column as text and as it is mapped to a mesaure then a numerical value is desirable.
Assuming that all of the data is valid for the conversion (i.e. just numbers as ‘text’), then in this specific case (as with Pay Date) the best action is to do nothing. Yes there is a penatly in build time for the conversion, but making an additional transform column which only does the conversion gives a bigger penalty.
However if it was mapped to a Dimension or a Property then matching the datatypes as described by Jon (i.e. change the type on the Dimension or Property) can very significantly boost the build times.
