Forum Discussion

ritanoori's avatar
ritanoori
Icon for Resolver I rankResolver I
5 years ago
Solved

Custom column - if first 9 characters column is alphanumeric or numeric

Hello Everyone

I'm trying to create a custom column in power querey where if the first 9 characters of [description] is alphanumeric then "Y" else  if the first 9 characters of [description] is numeric then "X" else "N/A"

 

can you please help. Thanks. 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    ritanoori , Try something like

    if Value.Is(Text.Start([description],9) Int64.Type) then "Y" else if Value.Is(Text.Start([description],9) Text) then "X" else "N/A"

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ritanoori , Try a new column like

     

    new column =
    var _chk = left([description],9)
    return
    Switch(True(),
    ISNUMBER(_chk), "Y"
    ISTEXT(_chk) ,"X"
    ,"N/A")

    • ritanoori's avatar
      ritanoori
      Icon for Resolver I rankResolver I
      5 years ago

      Thanks. But I need this in power querey. 

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        ritanoori , Try something like

        if Value.Is(Text.Start([description],9) Int64.Type) then "Y" else if Value.Is(Text.Start([description],9) Text) then "X" else "N/A"

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi ritanoori ,

    You can create this custom column in power query:

        Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each if 
            Value.Is(
                 Value.FromText(Text.Start([description],9)),Int64.Type
            ) 
        then "X"
        else if 
        Text.Length(
            Text.Remove(
                Text.Start([description],9),
                {"a".."z","A".."Z"})
            ) = 0
        then "Y"
        else "N/A"
    )

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.