Value returns null and error from text to numbers in query

willwall

New Member
Joined
Sep 22, 2016
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
clip_image001.gif


Thanks for your help, really appreciated. ?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top