cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbartlett
Helper III
Helper III

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

11 REPLIES 11
hdzhendov
Frequent Visitor

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 "".
Anonymous
Not applicable

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.

yeah tried that as well and failed... did a find replace of the <div> and replaced with nothing and same with </div>... it actually removed all data.. and do not leave the numbers remaining

 

 

Anonymous
Not applicable

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)

 

It does not recognise the commands 

 

Capture.PNG

Anonymous
Not applicable

That is a Power Query formula.  Click on "Edit Queries" and find the query with your table of information.  Select "Add Column" in the ribbon and find "Custom Column".  There is a box for the column name and then a box for that particular formula.  Once you click ok, you'll see a new version of the table with an attempt at processing the formula.  Any errors will be displayed and you can tweak the formula to get it correct.

 

Close and Apply will attempt to import your data again with this new column included.

I'm resorting to trying to go back to the source data and fix at that end......

anandav
Skilled Sharer
Skilled Sharer

@jbartlett,

 

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

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)

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors