Forum Discussion
Error : Cannot convert value " of type Text to type Integer
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
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
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.