Forum Discussion
Error : Cannot convert value " of type Text to type Integer
Skilled SharerAnonymous,
Try creating a new column with VALUE() DAX function:
Column = VALUE(Table1[Text])
This should convert the text to number.
Aletrnatively you can do your text cleanup (substitute, trim, etc.) in a variable and then finally return the number.
Column =
VAR TestVar = TRIM(Table1[Text])
RETURN
VALUE(Table1[Text])
Hope this helps.
Is this solves your problem please mark as solution.
tried the value function in new column.... failed
- anandav
Can you check whether you have any garbage values in your column like "
What I have been doing (as a means to get to the bottom of it) is a serires of calculated columns to ensure each step works...
Step one was a column removing all garbage prior to the numbers which was
Right('Table1[text],LEN('Table1[text])-8)
NOTE: in the column with the currupt data not every row is populated is I started the above with a "if "" do ""
I had to go minus 8 in the above formula to get rid of wrap text... the data had 2 blank lines then on the 3 line it had the text.
This succesfully created a column from
<div>116</div> changed data to 116</div>
<div>18</div> changed data to 18</div>
In the second calculated column I did a substitute function to substitute </div> with nothing (so "").
Then just as an extra layer (but probably not needed) I did a trim function to ensure no random spaces were left in the data.
From there I'm left with a column that appears to be just the numbers but is text and I cannot conver to a number (or value)