Value returns null and error from text to numbers in query

[willwall]

Hello
I have imported a text file to excel using power query. I want to convert a columns value from text to numbers but when i do so it returns "null" and "error" values throughout the column. I am using excel 2016. Gratefull for any help i can get

 

[sijpie]

How are you trying to convert the column? Using formula?(which formula?) or using vba?

Formula:
=NUMBERVALUE(B2)
will succesfully convert numbers as long as there are no non numerical characters in the text (other than separators or a space)

=VALUE(B2)
will only convert numbers consisting soly of numerical characters (plus separators)

If you think your text numbers consists solely of numbers, there may be 'hidden' spaces in front or behind. You could use =TRIM() to get rid of these.

Let me know

 

[willwall]

I am using formula but have not much experience of query.
This is what i wrote
= Table.TransformColumnTypes(#"Trimmed Text",{{"Sales", Int64.Type}})
The column i want to change to numbers is called "sales". I trimmed it and then tried to change it to whole numbers, it returns "error".

 

[sijpie]

tried to change it to whole numbers

Yes, but how did you try to do this?

 

[willwall]

I have done it in the formula bar in power query editor. I went to "view tab" above the ribbon and checked the box for formula bar, the box appears and there i have written the formula.

 

[sijpie]

Well after you have done your import. Add a column to your table.
Let's say that the column you want to convert is K, then in the new column you put the formula
=VALUE(K1)
Then copy this down.

Does that do the trick?

 

[willwall]

Yes, this works perfect.
I found another way also which is a bit easier since I am new to query.
When I import the text, I mark the column I want to change. Then I go to the ribbon and choose "data type" whole numbers. This changes the column to numbers but because there is text also in the column it returns the value "error" where the text is. This error value I have removed using a filter. Pretty basic I Know but still learning.

Thanks for your help, really appreciated. ?