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

[ritanoori]

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. 

[v-yingjl]

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.

[amitchandak]

@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]

Thanks. But I need this in power querey. 

[amitchandak · Accepted solution]

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