Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Extract first n characters if only contains numeric value

There are values in my column that are both numeric and alphabetical. If the first 4 characters are numbers, I must separate them. If not, I must leave the value alone.

Please consider the following, for instance:

 

1111 Grape Grape
9999 Apple Apple
8888 18 Mango  18 Mango
7777 Orange 10  Orange 10
Strawberry Fruit    Strawberry Fruit

Please advise?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated column.

     

     

     

    Expected result CC =
VAR _length =
    LEN ( Data[Column A] )
RETURN
    IF (
        NOT ISERROR ( INT ( LEFT ( Data[Column A], 4 ) ) ),
        RIGHT ( Data[Column A], _length - 5 ),
        Data[Column A]
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated column.

     

     

     

    Expected result CC =
VAR _length =
    LEN ( Data[Column A] )
RETURN
    IF (
        NOT ISERROR ( INT ( LEFT ( Data[Column A], 4 ) ) ),
        RIGHT ( Data[Column A], _length - 5 ),
        Data[Column A]
    )