If statement to convert text to value - Power Query
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,678
    Post Thanks / Like
    Mentioned
    27 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
    impossible things we do on the spot. for miracles you need to wait for a while

  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,678
    Post Thanks / Like
    Mentioned
    27 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
    impossible things we do on the spot. for miracles you need to wait for a while

  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,678
    Post Thanks / Like
    Mentioned
    27 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
    impossible things we do on the spot. for miracles you need to wait for a while

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
  •