Results 1 to 7 of 7

Thread: Value returns null and error from text to numbers in query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Value returns null and error from text to numbers in query

    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

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    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
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    New Member
    Join Date
    Sep 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    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".

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    tried to change it to whole numbers
    Yes, but how did you try to do this?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  5. #5
    New Member
    Join Date
    Sep 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    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.

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    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?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  7. #7
    New Member
    Join Date
    Sep 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Value returns null and error from text to numbers in query

    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. [IMG]file:///C:/Users/willi/AppData/Local/Temp/msohtmlclip1/01/clip_image001.gif[/IMG]

    Thanks for your help, really appreciated. 😊

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •