Forum Discussion
Error : Cannot convert value " of type Text to type Integer
Hi,
Can someone please explain (in a non technical way) how I fix the above error
I have data coming through sa listed below
<div>116</div>
<div>18</div>
I'm unable to change the data source so I need to fix it wthin Power BI desktop. For the above data I'm after the numbers 116 and 18.
I have created a column that looks at the data and with a series of RIGHT function, LEN function and SUBSTITUDE function formulas I now have a column with just the numbers however it is still recognised as text and not a number.
I have tried converting the column from text to number and it fails given the the error in the subject line of this message.
Any help would be appreciated
12 Replies
I would suggest fixing this issue from inside the Edit Queries section. What you need to do is to remove the <div> and the </div>. Then you will be left with something you can convert to Number.
The easiest method is to open Edit Queries. Find the query that contains these values. Click on the column with these values and do this:
- Select the column with a left click
- In the Home area of the ribbon, locate "Replace Values". Put <div> in the first box and leave the second box empty
- Repeat the last step again but use </div> in the first box.
- Now click on the icon on the left hand side of the column name and change the datatype to Whole Number.
- Close and apply.
You must have made an error. The Replace Values function only replaces the substring that you give it.
A more technical solution would be "Add Column" -> Custom Column. Call the field whatever name you like and have the both be:
= Text.Start(Text.End(Text.Trim([NumberField]), Text.Length(Text.Trim([NumberField])) - 5, Text.Length(Text.Trim([NumberField])) - 11)
Finally! I've been working on this for way too long.
My issue wasn't specifically the "<div>" existing in my table, but this suggestion sent me down the path of considering some possible blank or null rows in the source (I had assumed there were none). Once I filtered out all blank rows, my date conversion worked perfect.
Thank you!
- anandav
Skilled Sharer
Anonymous,
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.
- anandav
Can you check whether you have any garbage values in your column like "
- This conversation is pretty old, but it may be valuable for someone. I had the same problem, but the source for me was excel spreadsheet. In Query Editor everything was looking good, but in Power BI I had some blank rolls at the top. The blank cells were the problem for the error. VALUE() seems can't figure out what to do with empty strings like "".
