Results 1 to 6 of 6

Thread: If statement to convert text to value - Power Query

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    Prague, Czechia
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If statement to convert text to value - Power Query

    Hi,

    I am having trouble converting an excel formula into a power query statement.

    The formula is =VALUE(IF(ISNUMBER(VALUE(MID([DocNum],6,1))),[DocNum],LEFT([DocNum],5)))

    I have it in a custom column, but would like it to ideally be used as a formula in the [DocNum] column in power query if that is possible.

    The issue I have is that there a few entries in the source data where there is a document number which will have a letter suffix. (123A, 234A, etc) when the source data is formatted as a text, any entry which has the letter suffix imports as an error and a null cell in power query.

    How do I get around this where I can simply drop the suffix letter and import the numbers before that.

    Thanks!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: If statement to convert text to value - Power Query

    maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Prague, Czechia
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement to convert text to value - Power Query

    Quote Originally Posted by sandy666 View Post
    maybe try to add custom column: Text.Trim([raw],{"A".."Z","a".."z"})
    Thank you for replying.

    It is throwing an error and I think that might be because 99.9% of the data in that column is in fact a number, but the data i need this to work on is stored as text.

    The raw data is in an excel sheet if that helps.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: If statement to convert text to value - Power Query

    did you change data type to text in source column?

    adapt to your needs:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TypeText = Table.TransformColumnTypes(Source,{{"raw", type text}}),
        Numbers = Table.AddColumn(TypeText, "Numbers", each Number.FromText(Text.Trim([raw],{"A".."Z","a".."z"})))
    in
        Numbers
    raw Numbers
    123A
    123
    234B
    234
    345
    345
    AB567CD
    567
    Last edited by sandy666; Jul 7th, 2019 at 09:29 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Location
    Prague, Czechia
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If statement to convert text to value - Power Query

    Quote Originally Posted by sandy666 View Post
    did you change data type to text in source column?

    adapt to your needs:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TypeText = Table.TransformColumnTypes(Source,{{"raw", type text}}),
        Numbers = Table.AddColumn(TypeText, "Numbers", each Number.FromText(Text.Trim([raw],{"A".."Z","a".."z"})))
    in
        Numbers
    [COLOR=#FFFFFF ]raw[/COLOR] [COLOR=#FFFFFF ]Numbers[/COLOR]
    123A
    123
    234B
    234
    345
    345
    AB567CD
    567

    I had to customise this for my needs, but it worked perfectly. Thanks so much.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: If statement to convert text to value - Power Query

    Quote Originally Posted by bluelabel View Post
    I had to customise this for my needs
    That's what I said

    Quote Originally Posted by bluelabel View Post
    it worked perfectly.
    Glad to help

    Quote Originally Posted by bluelabel View Post
    Thanks so much.
    You are welcome
    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

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
  •