Hi there.
I need to convert a SQL column from nvarchar (Weight column) to float to excecute some calculations in a a PowerTable. I attach next screen .
I´m using next sentence, but it shows an error: "Error converting data type nvarchar to float"
Could you support to solve this issue?
Thanks in advance
Oh no. That weight column should have been a float to begin with and named weight_kg or similar. If possible I would make this change in the database and not by just converting it to a in your SELECT query.
However, I know that's not always possible, so I would say something like (depends on your db) SELECT CAST(REPLACE(REPLACE(Weight, 'kg', ''), ' ', '') as float) may work. Basically you need to strip that kg and any white space away before trying to cast it to a float. Syntax may be different depending on your flavor of db.
You need to remove the 'kg' from the value before converting.
CAST(REPLACE([Weight], 'kg', '') AS NUMERIC(10, 2)) AS ConvertedWeight
Hi, thanks for your feedbak, but the sentence shows next message: "Error converting data type nvarchar to numeric." 
You don't have a number in the database, you have a string.
Some people long ago thought that it was a great idea for computers to not care about the variable type - just store the data and let the computer figure it out at runtime.
Then they tried that for a while and discovered that it was a bad idea. They invented strong typing so that the programmer would have to make sure everything was right at design time rather than let it fail at run time.
New people came along and thought that was a lot of work and slowed down the developers. So they invented automatic type conversion.
Many of them figured out pretty quickly that was a bad idea and started calling it Evil Type Coercion. Sometimes there were compiler options to turn off the evil type coercion and force strict typing.
New people came along and thought that was a lot of extra work and slowed down the developers. Contracts can't change as quickly as the whims of the dreamers. They invented weakly typed variables and called it Duck Typing.
Some people realized that was a bad idea and invented a new language that tries to enforce strict typing over a duck typing language.
And then some people thought anonymous types would be a good idea and the pendulum keeps going back and forth.
At some point, some really smart people crashed their very expensive vehicle because some of them were using Miles and others were using Kilometers but the code didn't indicate the units. It was made obvious why numbers need units - either as a property of the object or in the variable name.
So, you have a variable that has units (a really good thing) but is no longer a number. Much of the infrastructure you are using would be happy to automatically convert a string that contains a number into a number (automatic type conversion) but you don't have a number in a string (or nvarchar). You have a combination of digits and characters.
Option 1:
Option 2:
Option 3:
If you do Option 1 then you can SELECT the number directly - no conversions are needed. You will not get the error.
If you do Option 2 then you can SELECT the string and it will be converted to a number for you automatically (probably by the database driver in Java, but you don't really care). You will not get the error.
If you do Option 3 then you will have to explicitly separate the number part of the column from the non-numeric part of the column (i.e., Parse). If the units are always kg then you can just throw them away in the actual SELECT statement. That is what the examples above are showing you. If you do that then the database server is doing the conversion for you.
MMaynard's solution doesn't remove the space in between. And possibly any other spaces you can't see. Try:
CAST(TRIM(REPLACE([Weight], 'kg', '')) AS NUMERIC(10, 2)) AS ConvertedWeight
EDIT: Just confirmed in SQLServer 2014 (you also didn't specify what db you're using) that it trims it automatically.
I would say, it doesn't matter if it's possible; a weight is a float, not a string. A weight should have never been made a string. Changing it is the only option. Not changing it has more drawbacks than trying to use regex to parse HTML (if you havent seen it already)
You change it and then find some mission critical external service was counting on it being a string (happened to me...)
I would recommend changing, just make sure to talk to the people who all touch the system to see if anyone relies on it in it's current state.
This can be dealt with by changing the table's name, fixing the structural problems, then creating a view with the original table name that re-assembles the rows into the old structure. Typical legacy apps won't notice.
Yea that's a good way to handle it and one of the examples I give as to why you should not name your database tables like "weightTable" - you never know when it will need to change object type Lest you be left with a confusingly named view.
You should name it "machineAWeightKgTable" instead 
and combine all your fields into a single one with id, t_stamp, and weight-with-units all combined: "12023-08-29 08:40:00.00045kg"
See? Then you can regex it!
Until someone puts XML in it.